I have built an Employee Management System using Laravel 5.7. Calculating salary works fine for 100-150 employees (but taking a long time to process the data) & for more than 150 employees it is showing a time out error.
I want to add the feature to calculate the salary of all employees in one go & also reduce the processing time for the same. Would it be appropriate to use chunk() for that? If yes, how do I implement that?
Note: The web application is for a BPO so there are so many types of logic to apply while calculating a salary. The code is too lengthy to share here, but if anyone wants to see the code, I can share it.
Front-end form screenshot
Page after calculation
<?php
public function calculate_salary(Request $request){
$this->validate($request,[
'employee_id' => 'required',
'attendance_types' => 'required',
'month' => 'required_without:choose_date',
'choose_date' => 'required_without:month'
]);
$download_salary = false;
$submit_salary = false;
if ($request->has('submit_data'))
{
$submit_salary = true;
}
else
{
$submit_salary = false;
}
if ($request->has('download_data'))
{
$download_salary = true;
}
else
{
$download_salary = false;
}
$dept = $request->input('salary_department');
$role = $request->input('salary_role');
$process = $request->input('salary_process');
$salary_dept = Department::where('id', $dept)->pluck('dept_name')->first();
$salary_role = Role::where('id', $role)->pluck('role_name')->first();
$salary_process = Process::where('id', $process)->pluck('process_name')->first();
if ($submit_salary == true || $download_salary == true)
{
$months = $request->input('month');
$month = $months[0];
$employee_ids = $request->input('employee_id');
$attendance_types = $request->input('attendance_types');
$attendance_type_temp_insert = $attendance_types[0];
$attendance_type = explode(',', $attendance_type_temp_insert);
if (!empty($request->input('choose_date')))
{
$dates = $request->input('choose_date');
$date = $dates[0];
}
else
{
$date = "";
}
}
else
{
$month = $request->input('month');
$employee_ids = $request->input('employee_id');
$attendance_type = $request->input('attendance_types');
$attendance_type_temp_insert = implode(',', $request->input('attendance_types'));
$date = $request->input('choose_date');
}
// Get start & end dates
$data = $this->get_salary_dates($month, $date);
$start_date = $data['start_date'];
$end_date = $data['end_date'];
$salary_start_date = $start_date->toDateString();
$salary_end_date = $end_date->toDateString();
$working_days_by_month = $this->get_working_days_of_given_month($start_date, $end_date);
$working_days = $working_days_by_month['working_days'];
$no_days = $working_days_by_month['no_days'];
Schema::create('temp_salary', function (Blueprint $table) {
$table->increments('id');
$table->integer('employee_id');
$table->string('employee_name');
$table->integer('working_days');
$table->float('emp_working_days');
$table->float('basic');
$table->float('hra');
$table->float('conveyance');
$table->float('spcl_inc');
$table->float('gross_salary');
$table->float('pf');
$table->float('esi');
$table->integer('headphone');
$table->integer('id_card');
$table->float('deductions');
$table->integer('pl_balance');
$table->integer('combo_balance');
$table->float('net_payable');
$table->string('month')->nullable();
$table->string('attendance_types');
$table->string('choose_date')->nullable();
$table->string('department')->nullable();
$table->string('role')->nullable();
$table->string('process')->nullable();
$table->timestamps();
$table->temporary();
});
$csv[] = ['Employee ID', 'Name','Department','Role','Process','Bank','Bank Account Number', 'Basic', 'HRA', 'Conveyance', 'Spcl Inc', 'Gross Salary','PF', 'ESI', 'Deductions', 'Total Working Days', 'Net Payable','Salary Month', 'Salary Start Date','Salary End Date'];
$csv1[] = ['Employee ID', 'Name','Department','Role','Process','Bank','Bank Account Number', 'Basic', 'HRA', 'Conveyance', 'Spcl Inc', 'Gross Salary','PF', 'ESI', 'Deductions', 'P','H','A','WO','WOP','WOH','UAL','Paid Leaves','PL','Combo','ual_deduct','Total Working Days', 'Net Payable','Salary Month', 'Salary Start Date','Salary End Date'];
foreach($employee_ids as $id) {
if($submit_salary == true){
DB::table('attendances')->where('employee_id', '=', $id)->whereBetween('attendance_date', [$start_date, $end_date])->update(['salary_status' => 1]);
}
$employee = Employee::find($id);
$name = $employee->name;
$emp_role = $employee->empRole->role_name;
$emp_department = $employee->empDepartment->dept_name;
$emp_process = $employee->empProcess->process_name;
$bank_account_number = $employee->bank_account_number;
$bank_name = $employee->bank_name;
$salary = Salary::where('employee_id', $id)->first();
$pl_balance = $salary->pl_balance;
$combo_balance = $salary->combo_balance;
$attendances = DB::table('attendances')->where('employee_id', '=', $id)->get();
if ($attendances->count() > 0)
{
foreach($attendances as $attendance)
{
$att_id = $attendance->id;
$att_date = Carbon::parse($attendance->attendance_date);
$dialer_in = strtotime($attendance->dialer_in_time);
$dialer_out = strtotime($attendance->dialer_out_time);
$bio_in = strtotime($attendance->biometric_in_time);
$bio_out = strtotime($attendance->biometric_out_time);
$bio_diff = $bio_out - $bio_in;
$crm_in = strtotime($attendance->crm_in_time);
$crm_out = strtotime($attendance->crm_out_time);
$week_off = $attendance->week_off;
$combo = $attendance->combo;
$holiday = $attendance->holiday;
$ual = $attendance->ual;
$dialer_duration = $attendance->dialer_difference;
$bio_duration = $attendance->biometric_difference;
$crm_duration = $attendance->crm_difference;
$chkatt = $this->get_attendance($attendance_type, $attendance->dialer_in_time, $attendance->dialer_out_time,$attendance->crm_in_time,$attendance->crm_out_time, $dialer_duration, $bio_duration, $crm_duration);
$attendance_status = $this->attendance_status($id, $chkatt, $holiday, $week_off, $ual);
DB::table('attendances')->where('id', $att_id)->update(['attendance_status' => $attendance_status]);
//echo $attendance_status;
}
}
$totalWorkingDays = $this->get_work_days($id,$start_date,$end_date,$combo_balance,$pl_balance, $submit_salary);
$totalWorkingDays = number_format((float)$totalWorkingDays, 2, '.', '');
$salary_data = $this->get_calculated_salary_data($no_days, $totalWorkingDays, $id, $submit_salary);
$basic = $salary_data['basic'];
$hra = $salary_data['hra'];
$conveyance = $salary_data['conveyance'];
$spcl_inc = $salary_data['spcl_inc'];
$gross_salary = $salary_data['gross_salary'];
$pf = $salary_data['pf'];
$esi = $salary_data['esi'];
$hp_charges = $salary_data['hp_charges'];
$idcard_charges = $salary_data['idcard_charges'];
$deductions = $salary_data['deductions'];
$net_payable = $salary_data['net_payable'];
DB::table('temp_salary')->insert(['employee_id' => $id, 'employee_name' => $name, 'working_days' => $working_days, 'emp_working_days' => $totalWorkingDays, 'basic' => $basic, 'hra' => $hra, 'conveyance' => $conveyance, 'spcl_inc' => $spcl_inc, 'gross_salary' => $gross_salary, 'pf' => $pf, 'esi' => $esi, 'headphone' => $hp_charges, 'id_card' => $idcard_charges, 'deductions' => $deductions, 'pl_balance' => $pl_balance, 'combo_balance' => $combo_balance, 'net_payable' => $net_payable, 'month' => $month, 'attendance_types' => $attendance_type_temp_insert, 'choose_date' => $date, 'department' => $salary_dept, 'role' => $salary_role, 'process' => $salary_process]);
$csv[] = [$id, $name, $emp_department, $emp_role, $emp_process, $bank_name, $bank_account_number, $basic, $hra, $conveyance, $spcl_inc, $gross_salary, $pf, $esi, $deductions, $totalWorkingDays, $net_payable, $month, $start_date, $end_date];
$get_days_status = $this->get_days_status($id, $start_date, $end_date);
$pCount = $get_days_status['pCount'];
$HalfDaysCount = $get_days_status['hCount'];
$lateLeaveCount = $get_days_status['late_leave'];
$wopCount = $get_days_status['wopCount'];
$leaves_count = $get_days_status['leaves_count'];
$UalCount = $get_days_status['UalCount'];
$woCount = $get_days_status['woCount'];
$wohCount = $get_days_status['wohCount'];
$ual_deduct = ($UalCount * 1.5) - $UalCount;
$get_paid_leaves_row = $get_days_status['pl_leaves'];
$csv1[] = [$id, $name, $emp_department, $emp_role, $emp_process, $bank_name, $bank_account_number, $basic, $hra, $conveyance, $spcl_inc, $gross_salary, $pf, $esi, $deductions, $pCount,$HalfDaysCount,$leaves_count,$woCount,$wopCount,$wohCount,$UalCount,$get_paid_leaves_row,$pl_balance, $combo_balance,$ual_deduct, $totalWorkingDays, $net_payable, $month, $start_date, $end_date];
}
$datas = DB::table('temp_salary')->get();
if($submit_salary == true){
return Excel::create('Employee_salary_report', function($excel) use ($csv) {
$excel->sheet('Employee_salary_report', function($sheet) use ($csv) {
$sheet->fromArray($csv, null, 'A1', false, false)
->getStyle('A1')
->getAlignment()
->setWrapText(true);
});
})->download('csv');
}
if($download_salary == true){
return Excel::create('salary_report', function($excel) use ($csv1) {
$excel->sheet('salary_report', function($sheet) use ($csv1) {
$sheet->fromArray($csv1, null, 'A1', false, false)
->getStyle('A1')
->getAlignment()
->setWrapText(true);
});
})->download('csv');
}
Schema::drop('temp_salary');
$departments = Department::all();
$processes = Process::all();
$roles = Role::all();
return view('sys_mg.salaries.get-salary')->with(['datas'=>$datas,'departments' => $departments, 'processes' => $processes, 'roles' => $roles, 'salary_dept' => $salary_dept, 'salary_role' => $salary_role, 'salary_process'=>$salary_process, 'salary_month'=>$month,'salary_startDate' => $salary_start_date, 'salary_endDate' => $salary_end_date, 'attendance_check_type' => $attendance_type]);
}
