$this->shift(), "category" => $this->category(), "site" => $this->site(), "section" => $this->section(), "department" => $this->department(), "company" => $this->company(), "team" => $this->team(), "employee" => $this->allEmployee(), ]; $responseBody = $this->responseBody(true, "AllSettings", "all", ["settings" => $settings]); } catch (\Exception $exception) { $responseBody = $this->responseBody(false, "AllSettings", "error", $exception); } return response()->json(["data" => $responseBody]); } public function shift() { try { $shift = AttShift::all(); $collection = []; for ($i = 0; $i < count($shift); $i++) { array_push($collection, ["id" => $shift[$i]['shift_id'], "value" => $shift[$i]['shift_name']]); } return $collection; } catch (\Exception $exception) { return $exception; } } private function category() { try { $category = EmployeeCategory::all(); $collection = []; for ($i = 0; $i < count($category); $i++) { array_push($collection, ["id" => $category[$i]['category_id'], "value" => $category[$i]['category']]); } return $collection; } catch (\Exception $exception) { return $exception; } } private function site() { try { $site = EmployeeSite::all(); $collection = []; for ($i = 0; $i < count($site); $i++) { array_push($collection, ["id" => $site[$i]['site_id'], "value" => $site[$i]['site']]); } return $collection; } catch (\Exception $exception) { return $exception; } } private function department() { try { $department = EmployeeDepartment::all(); $collection = []; for ($i = 0; $i < count($department); $i++) { array_push($collection, ["id" => $department[$i]['department_id'], "value" => $department[$i]['department']]); } return $collection; } catch (\Exception $exception) { return $exception; } } private function section() { try { $section = EmployeeSection::all(); $collection = []; for ($i = 0; $i < count($section); $i++) { array_push($collection, ["id" => $section[$i]['section_id'], "value" => $section[$i]['section']]); } return $collection; } catch (\Exception $exception) { return $exception; } } public function subSection($section_id) { try { $subsection = EmployeeSubsection::where('section_id', '=', $section_id)->get(); $collection = []; for ($i = 0; $i < count($subsection); $i++) { array_push($collection, ["id" => $subsection[$i]['subsection_id'], "value" => $subsection[$i]['subsection']]); } $responseBody = $this->responseBody(true, "Subsection", "all", $collection); } catch (\Exception $exception) { $responseBody = $this->responseBody(false, "Subsection", "error", $exception); } return response()->json(["data" => $responseBody]); } private function company() { try { $company = Company::all(); $collection = []; for ($i = 0; $i < count($company); $i++) { array_push($collection, ["id" => $company[$i]['company_id'], "value" => $company[$i]['company']]); } return $collection; } catch (\Exception $exception) { return $exception; } } private function team() { try { $team = Team::all(); $collection = []; for ($i = 0; $i < count($team); $i++) { array_push($collection, ["id" => $team[$i]['team_id'], "value" => $team[$i]['team_name']]); } return $collection; } catch (\Exception $exception) { return $exception; } } private function allEmployee() { try { $employees = Employee::all(); $collection = []; for ($i = 0; $i < count($employees); $i++) { array_push($collection, ["img" => $employees[$i]['photo_parth'], "id" => $employees[$i]['employee_id'], "value" => $employees[$i]['name_withinitial']]); } return $collection; } catch (\Exception $exception) { return $exception; } } public function dailyOTReport($setting, $date) { try { $title = ''; $setting_aaray = json_decode($setting); $category = $setting_aaray->category; $site = $setting_aaray->site; $department = $setting_aaray->department; $section = $setting_aaray->section; $subsection = $setting_aaray->subsection; $company = $setting_aaray->company; $team = $setting_aaray->team; $employee = $setting_aaray->employee; $query = "SELECT hr_employees.employee_id, hr_employees.name_withinitial AS employee_name, hr_teams.team_name, hr_att_shifts.shift_name AS shift, hr_att_time.InTime AS in_time, hr_att_time.OutTime AS out_time, hr_att_time.N_overtime AS n_ot, hr_att_time.D_overtime AS d_ot, ((hr_att_time.N_overtime * hr_salary_data.ot_rate1_amount)+(hr_att_time.D_overtime * hr_salary_data.ot_rate2_amount)) AS ot_amount FROM hr_att_schedules INNER JOIN hr_att_time ON hr_att_schedules.employee_id = hr_att_time.EmployeeID AND hr_att_schedules.schedule_date_from = hr_att_time.Date INNER JOIN hr_employees ON hr_att_schedules.employee_id = hr_employees.employee_id INNER JOIN hr_att_shifts ON hr_att_schedules.shift_id = hr_att_shifts.shift_id INNER JOIN hr_salary_data ON hr_employees.employee_id = hr_salary_data.employee_id LEFT JOIN hr_employee_teams ON hr_employees.employee_id = hr_employee_teams.employee_id LEFT JOIN hr_teams ON hr_employee_teams.team_id = hr_teams.team_id WHERE "; if ($category != '') { $query .= "hr_employees.category_id = '" . $category . "' AND "; $title = 'Category : ' . EmployeeCategory::find($category)->category; } if ($site != '') { $query .= "hr_employees.site_id = '" . $site . "' AND "; $title = 'Site : ' . EmployeeSite::find($site)->site; } if ($department != '') { $query .= "hr_employees.department_id = '" . $department . "' AND "; $title = 'Department : ' . EmployeeDepartment::find($department)->department; } if ($section != '') { $query .= "hr_employees.section_id = '" . $section . "' AND "; $title = 'Section : ' . EmployeeSection::find($section)->section; } if ($subsection != '') { $query .= "hr_employees.subsection_id = '" . $subsection . "' AND "; $title = 'Subsection : ' . EmployeeSubsection::find($subsection)->subsection; } if ($company != '') { $query .= "hr_employees.company_id = '" . $company . "' AND "; $title = 'Company : ' . Company::find($company)->company; } if ($team != '') { $query .= "hr_employee_teams.team_id = '" . $team . "' AND "; $title = 'Team : ' . Team::find($team)->team_name; } if ($employee != '') { $query .= "hr_employees.employee_id = '" . $employee . "' AND "; $title = 'Employee : ' . Employee::find($employee)->name_withinitial; } $query .= " hr_att_schedules.schedule_date_from = '" . $date . "' AND (hr_att_time.InTime AND hr_att_time.OutTime) ORDER BY convert(hr_employees.employee_id, decimal) ASC"; //$query = preg_replace('/\W\w+\s*(\W*)$/', '$1', $query); $employees = DB::select($query); $responseBody = $this->responseBody(true, "EmployeeDetailReportController", "Report", ['body' => $employees, 'title' => $title, 'report_date' => $date, "query" => $query]); } catch (Exception $exception) { $responseBody = $this->responseBody(false, "EmployeeDetailReportController", "Report", $exception); } return response()->json(["data" => $responseBody]); } public function highOTReopt($setting, $date_from, $date_to) { try { $title = ''; $setting_aaray = json_decode($setting); $category = $setting_aaray->category; $site = $setting_aaray->site; $department = $setting_aaray->department; $section = $setting_aaray->section; $subsection = $setting_aaray->subsection; $company = $setting_aaray->company; $team = $setting_aaray->team; $employee = $setting_aaray->employee; $query = "SELECT hr_employees.employee_id, hr_employees.name_withinitial AS employee_name, hr_teams.team_name, hr_employee_designations.designation, hr_att_shifts.shift_name AS shift, SUM(hr_att_time.N_overtime) AS n_ot, (SUM(hr_att_time.N_overtime) * hr_salary_data.ot_rate1_amount) AS n_ot_cost, SUM(hr_att_time.D_overtime) AS d_ot, (SUM(hr_att_time.D_overtime) * hr_salary_data.ot_rate2_amount) AS d_ot_cost FROM hr_att_schedules INNER JOIN hr_att_time ON hr_att_schedules.employee_id = hr_att_time.EmployeeID AND hr_att_schedules.schedule_date_from = hr_att_time.Date INNER JOIN hr_employees ON hr_att_schedules.employee_id = hr_employees.employee_id INNER JOIN hr_att_shifts ON hr_att_schedules.shift_id = hr_att_shifts.shift_id INNER JOIN hr_salary_data ON hr_employees.employee_id = hr_salary_data.employee_id INNER JOIN hr_employee_designations ON hr_employees.designation_id = hr_employee_designations.designation_id LEFT JOIN hr_employee_teams ON hr_employees.employee_id = hr_employee_teams.employee_id LEFT JOIN hr_teams ON hr_employee_teams.team_id = hr_teams.team_id WHERE "; if ($category != '') { $query .= "hr_employees.category_id = '" . $category . "' AND "; $title = 'Category : ' . EmployeeCategory::find($category)->category; } if ($site != '') { $query .= "hr_employees.site_id = '" . $site . "' AND "; $title = 'Site : ' . EmployeeSite::find($site)->site; } if ($department != '') { $query .= "hr_employees.department_id = '" . $department . "' AND "; $title = 'Department : ' . EmployeeDepartment::find($department)->department; } if ($section != '') { $query .= "hr_employees.section_id = '" . $section . "' AND "; $title = 'Section : ' . EmployeeSection::find($section)->section; } if ($subsection != '') { $query .= "hr_employees.subsection_id = '" . $subsection . "' AND "; $title = 'Subsection : ' . EmployeeSubsection::find($subsection)->subsection; } if ($company != '') { $query .= "hr_employees.company_id = '" . $company . "' AND "; $title = 'Company : ' . Company::find($company)->company; } if ($team != '') { $query .= "hr_employee_teams.team_id = '" . $team . "' AND "; $title = 'Team : ' . Team::find($team)->team_name; } if ($employee != '') { $query .= "hr_employees.employee_id = '" . $employee . "' AND "; $title = 'Employee : ' . Employee::find($employee)->name_withinitial; } $query .= " hr_att_schedules.schedule_date_from BETWEEN '" . $date_from . "' AND '" . $date_to . "' AND (hr_att_time.InTime AND hr_att_time.OutTime) GROUP BY hr_employees.employee_id ORDER BY convert(hr_employees.employee_id, decimal) ASC"; //$query = preg_replace('/\W\w+\s*(\W*)$/', '$1', $query); $employees = DB::select($query); $responseBody = $this->responseBody(true, "EmployeeDetailReportController", "Report", ['body' => $employees, 'title' => $title, 'report_date' => $date_from . " To " . $date_to]); } catch (Exception $exception) { $responseBody = $this->responseBody(false, "EmployeeDetailReportController", "Report", $exception); } return response()->json(["data" => $responseBody]); } public function monthlyOTSummaryReport($setting, $date_from, $date_to) { try { $title = ''; $setting_aaray = json_decode($setting); $category = $setting_aaray->category; $site = $setting_aaray->site; $department = $setting_aaray->department; $section = $setting_aaray->section; $subsection = $setting_aaray->subsection; $company = $setting_aaray->company; $team = $setting_aaray->team; $employee = $setting_aaray->employee; $query = "SELECT hr_employees.employee_id, IF(hr_employee_categories.category_id = 5, IF(SUM(hr_att_time.NoPay)=0, hr_employee_categories.work_days_month, IF(SUM(hr_att_time.Worked_Day) > hr_employee_categories.work_days_month, hr_employee_categories.work_days_month, SUM(hr_att_time.Worked_Day) ) ), SUM(hr_att_time.Worked_Day) ) AS worked_day, (SELECT hr_employee_designations.designation FROM hr_employee_designations WHERE designation_id = hr_employees.designation_id) AS designation, (SELECT hr_teams.team_name FROM hr_teams WHERE hr_teams.team_id = hr_employee_teams.team_id) AS team_name, hr_employee_categories.category, hr_employees.name_withinitial AS employee_name, SUM(hr_att_time.Worked_Day) AS worked_day, SUM(hr_att_time.N_overtime) AS n_ot, (SUM(hr_att_time.N_overtime) * (SELECT hr_salary_data.ot_rate1_amount FROM hr_salary_data WHERE hr_salary_data.employee_id = hr_employees.employee_id AND hr_salary_data.salary_year = YEAR('" . $date_from . "') AND hr_salary_data.salary_month = MONTH('" . $date_from . "'))) AS n_ot_cost, SUM(hr_att_time.D_overtime) AS d_ot, (SUM(hr_att_time.D_overtime) * (SELECT hr_salary_data.ot_rate2_amount FROM hr_salary_data WHERE hr_salary_data.employee_id = hr_employees.employee_id AND hr_salary_data.salary_year = YEAR('" . $date_from . "') AND hr_salary_data.salary_month = MONTH('" . $date_from . "'))) AS d_ot_cost, 0 AS poya_ot, 0 AS p_ot_cost, (SUM(hr_att_time.N_overtime) + SUM(hr_att_time.D_overtime)) AS total_ot_hours, ( (SUM(hr_att_time.N_overtime) * (SELECT hr_salary_data.ot_rate1_amount FROM hr_salary_data WHERE hr_salary_data.employee_id = hr_employees.employee_id AND hr_salary_data.salary_year = YEAR('" . $date_from . "') AND hr_salary_data.salary_month = MONTH('" . $date_from . "'))) + (SUM(hr_att_time.D_overtime) * (SELECT hr_salary_data.ot_rate2_amount FROM hr_salary_data WHERE hr_salary_data.employee_id = hr_employees.employee_id AND hr_salary_data.salary_year = YEAR('" . $date_from . "') AND hr_salary_data.salary_month = MONTH('" . $date_from . "'))) ) AS total_ot_amount FROM hr_att_time INNER JOIN hr_employees ON hr_att_time.EmployeeID = hr_employees.employee_id INNER JOIN hr_employee_categories ON hr_employees.category_id = hr_employee_categories.category_id LEFT JOIN hr_employee_teams ON hr_employees.employee_id = hr_employee_teams.employee_id INNER JOIN hr_teams ON hr_employee_teams.team_id = hr_teams.team_id WHERE "; if ($category != '') { $query .= "hr_employees.category_id = '" . $category . "' AND "; $title = 'Category : ' . EmployeeCategory::find($category)->category; } if ($site != '') { $query .= "hr_employees.site_id = '" . $site . "' AND "; $title = 'Site : ' . EmployeeSite::find($site)->site; } if ($department != '') { $query .= "hr_employees.department_id = '" . $department . "' AND "; $title = 'Department : ' . EmployeeDepartment::find($department)->department; } if ($section != '') { $query .= "hr_employees.section_id = '" . $section . "' AND "; $title = 'Section : ' . EmployeeSection::find($section)->section; } if ($subsection != '') { $query .= "hr_employees.subsection_id = '" . $subsection . "' AND "; $title = 'Subsection : ' . EmployeeSubsection::find($subsection)->subsection; } if ($company != '') { $query .= "hr_employees.company_id = '" . $company . "' AND "; $title = 'Company : ' . Company::find($company)->company; } if ($team != '') { $query .= "hr_employee_teams.team_id = '" . $team . "' AND "; $title = 'Team : ' . Team::find($team)->team_name; } if ($employee != '') { $query .= "hr_employees.employee_id = '" . $employee . "' AND "; $title = 'Employee : ' . Employee::find($employee)->name_withinitial; } $query .= " hr_att_time.Date BETWEEN '" . $date_from . "' AND '" . $date_to . "' AND hr_employees.employeestatus_id != '4' GROUP BY hr_att_time.EmployeeID ORDER BY convert(hr_employees.employee_id, decimal) ASC"; //$query = preg_replace('/\W\w+\s*(\W*)$/', '$1', $query); $employees = DB::select($query); $responseBody = $this->responseBody(true, "EmployeeDetailReportController", "Report", ['body' => $employees, 'title' => $title, 'report_date' => $date_from . " To " . $date_to]); } catch (Exception $exception) { $responseBody = $this->responseBody(false, "EmployeeDetailReportController", "Report", $exception); } return response()->json(["data" => $responseBody]); } public function epf_etfReports($setting, $date) { try { $title = ''; $setting_aaray = json_decode($setting); $category = $setting_aaray->category; $site = $setting_aaray->site; $department = $setting_aaray->department; $section = $setting_aaray->section; $subsection = $setting_aaray->subsection; $company = $setting_aaray->company; $team = $setting_aaray->team; $employee = $setting_aaray->employee; $query = "SELECT hr_employees.employee_id, hr_employees.name_withinitial AS employee_name, hr_employees.epf_no, hr_employees.nic, @employer_epf :=hr_salary_data.employer_epf As employer_contribution, @employee_epf :=hr_salary_data.employee_epf As employee_contribution, (@employer_epf + @employee_epf) As total_contribution, hr_salary_data.employee_etf, (SUM(hr_salary_data.earn_01)+ SUM(hr_salary_data.earn_02)+ SUM(hr_salary_data.earn_03)+ SUM(hr_salary_data.earn_04)+ SUM(hr_salary_data.earn_05)+ SUM(hr_salary_data.earn_06)+ SUM(hr_salary_data.earn_07)+ SUM(hr_salary_data.earn_08)+ SUM(hr_salary_data.earn_09)+ SUM(hr_salary_data.earn_10)) AS total_earnings, SUM(hr_salary_data.worked_days) AS work_day FROM hr_employees LEFT JOIN hr_salary_data ON hr_employees.employee_id = hr_salary_data.employee_id LEFT JOIN hr_employee_categories ON hr_employees.category_id = hr_employee_categories.category_id LEFT JOIN hr_employee_teams ON hr_employees.employee_id = hr_employee_teams.employee_id LEFT JOIN hr_teams ON hr_employee_teams.team_id = hr_teams.team_id WHERE "; if ($category != '') { $query .= "hr_employees.category_id = '" . $category . "' AND "; $title = 'Category : ' . EmployeeCategory::find($category)->category; } if ($site != '') { $query .= "hr_employees.site_id = '" . $site . "' AND "; $title = 'Site : ' . EmployeeSite::find($site)->site; } if ($department != '') { $query .= "hr_employees.department_id = '" . $department . "' AND "; $title = 'Department : ' . EmployeeDepartment::find($department)->department; } if ($section != '') { $query .= "hr_employees.section_id = '" . $section . "' AND "; $title = 'Section : ' . EmployeeSection::find($section)->section; } if ($subsection != '') { $query .= "hr_employees.subsection_id = '" . $subsection . "' AND "; $title = 'Subsection : ' . EmployeeSubsection::find($subsection)->subsection; } if ($company != '') { $query .= "hr_employees.company_id = '" . $company . "' AND "; $title = 'Company : ' . Company::find($company)->company; } if ($team != '') { $query .= "hr_employee_teams.team_id = '" . $team . "' AND "; $title = 'Team : ' . Team::find($team)->team_name; } if ($employee != '') { $query .= "hr_employees.employee_id = '" . $employee . "' AND "; $title = 'Employee : ' . Employee::find($employee)->name_withinitial; } $year = explode("-", $date)[0]; $month = explode("-", $date)[1]; $query .= " hr_salary_data.salary_year = YEAR('" . $year . "') AND hr_salary_data.salary_month = MONTH('" . $month . "') GROUP BY employee_id ORDER BY convert(hr_employees.employee_id, decimal) ASC"; //$query = preg_replace('/\W\w+\s*(\W*)$/', '$1', $query); $employees = DB::select($query); $responseBody = $this->responseBody(true, "EmployeeDetailReportController", "Report", ['body' => $employees, 'title' => $title, 'report_date' => $date]); } catch (Exception $exception) { $responseBody = $this->responseBody(false, "EmployeeDetailReportController", "Report", $exception); } return response()->json(["data" => $responseBody]); } public function nightAllowanceReport($setting, $start_date, $end_date) { try { $title = ''; $setting_aaray = json_decode($setting); $category = $setting_aaray->category; $site = $setting_aaray->site; $department = $setting_aaray->department; $section = $setting_aaray->section; $subsection = $setting_aaray->subsection; $company = $setting_aaray->company; $team = $setting_aaray->team; $employee = $setting_aaray->employee; //$query = preg_replace('/\W\w+\s*(\W*)$/', '$1', $query); ini_set('max_execution_time', '0'); // for infinite time of execution $main_query = "SELECT hr_employees.employee_id,hr_employees.name_withinitial AS employee_name, @D1:=SUM(IF(hr_att_time.In_Status = 1 AND hr_att_time.Out_Status = 1,IF(DAY(hr_att_time.Date)='01',IF(hr_att_time.ShiftNo = 5,1,IF(hr_att_time.ShiftNo = 6,1,0)),0),0)) AS Day1, @D2:=SUM(IF(hr_att_time.In_Status = 1 AND hr_att_time.Out_Status = 1,IF(DAY(hr_att_time.Date)='02',IF(hr_att_time.ShiftNo = 5,1,IF(hr_att_time.ShiftNo = 6,1,0)),0),0)) AS Day2, @D3:=SUM(IF(hr_att_time.In_Status = 1 AND hr_att_time.Out_Status = 1,IF(DAY(hr_att_time.Date)='03',IF(hr_att_time.ShiftNo = 5,1,IF(hr_att_time.ShiftNo = 6,1,0)),0),0)) AS Day3, @D4:=SUM(IF(hr_att_time.In_Status = 1 AND hr_att_time.Out_Status = 1,IF(DAY(hr_att_time.Date)='04',IF(hr_att_time.ShiftNo = 5,1,IF(hr_att_time.ShiftNo = 6,1,0)),0),0)) AS Day4, @D5:=SUM(IF(hr_att_time.In_Status = 1 AND hr_att_time.Out_Status = 1,IF(DAY(hr_att_time.Date)='05',IF(hr_att_time.ShiftNo = 5,1,IF(hr_att_time.ShiftNo = 6,1,0)),0),0)) AS Day5, @D6:=SUM(IF(hr_att_time.In_Status = 1 AND hr_att_time.Out_Status = 1,IF(DAY(hr_att_time.Date)='06',IF(hr_att_time.ShiftNo = 5,1,IF(hr_att_time.ShiftNo = 6,1,0)),0),0)) AS Day6, @D7:=SUM(IF(hr_att_time.In_Status = 1 AND hr_att_time.Out_Status = 1,IF(DAY(hr_att_time.Date)='07',IF(hr_att_time.ShiftNo = 5,1,IF(hr_att_time.ShiftNo = 6,1,0)),0),0)) AS Day7, @D8:=SUM(IF(hr_att_time.In_Status = 1 AND hr_att_time.Out_Status = 1,IF(DAY(hr_att_time.Date)='08',IF(hr_att_time.ShiftNo = 5,1,IF(hr_att_time.ShiftNo = 6,1,0)),0),0)) AS Day8, @D9:=SUM(IF(hr_att_time.In_Status = 1 AND hr_att_time.Out_Status = 1,IF(DAY(hr_att_time.Date)='09',IF(hr_att_time.ShiftNo = 5,1,IF(hr_att_time.ShiftNo = 6,1,0)),0),0)) AS Day9, @D10:=SUM(IF(hr_att_time.In_Status = 1 AND hr_att_time.Out_Status = 1,IF(DAY(hr_att_time.Date)='10',IF(hr_att_time.ShiftNo = 5,1,IF(hr_att_time.ShiftNo = 6,1,0)),0),0)) AS Day10, @D11:=SUM(IF(hr_att_time.In_Status = 1 AND hr_att_time.Out_Status = 1,IF(DAY(hr_att_time.Date)='11',IF(hr_att_time.ShiftNo = 5,1,IF(hr_att_time.ShiftNo = 6,1,0)),0),0)) AS Day11, @D12:=SUM(IF(hr_att_time.In_Status = 1 AND hr_att_time.Out_Status = 1,IF(DAY(hr_att_time.Date)='12',IF(hr_att_time.ShiftNo = 5,1,IF(hr_att_time.ShiftNo = 6,1,0)),0),0)) AS Day12, @D13:=SUM(IF(hr_att_time.In_Status = 1 AND hr_att_time.Out_Status = 1,IF(DAY(hr_att_time.Date)='13',IF(hr_att_time.ShiftNo = 5,1,IF(hr_att_time.ShiftNo = 6,1,0)),0),0)) AS Day13, @D14:=SUM(IF(hr_att_time.In_Status = 1 AND hr_att_time.Out_Status = 1,IF(DAY(hr_att_time.Date)='14',IF(hr_att_time.ShiftNo = 5,1,IF(hr_att_time.ShiftNo = 6,1,0)),0),0)) AS Day14, @D15:=SUM(IF(hr_att_time.In_Status = 1 AND hr_att_time.Out_Status = 1,IF(DAY(hr_att_time.Date)='15',IF(hr_att_time.ShiftNo = 5,1,IF(hr_att_time.ShiftNo = 6,1,0)),0),0)) AS Day15, @D16:=SUM(IF(hr_att_time.In_Status = 1 AND hr_att_time.Out_Status = 1,IF(DAY(hr_att_time.Date)='16',IF(hr_att_time.ShiftNo = 5,1,IF(hr_att_time.ShiftNo = 6,1,0)),0),0)) AS Day16, @D17:=SUM(IF(hr_att_time.In_Status = 1 AND hr_att_time.Out_Status = 1,IF(DAY(hr_att_time.Date)='17',IF(hr_att_time.ShiftNo = 5,1,IF(hr_att_time.ShiftNo = 6,1,0)),0),0)) AS Day17, @D18:=SUM(IF(hr_att_time.In_Status = 1 AND hr_att_time.Out_Status = 1,IF(DAY(hr_att_time.Date)='18',IF(hr_att_time.ShiftNo = 5,1,IF(hr_att_time.ShiftNo = 6,1,0)),0),0)) AS Day18, @D19:=SUM(IF(hr_att_time.In_Status = 1 AND hr_att_time.Out_Status = 1,IF(DAY(hr_att_time.Date)='19',IF(hr_att_time.ShiftNo = 5,1,IF(hr_att_time.ShiftNo = 6,1,0)),0),0)) AS Day19, @D20:=SUM(IF(hr_att_time.In_Status = 1 AND hr_att_time.Out_Status = 1,IF(DAY(hr_att_time.Date)='20',IF(hr_att_time.ShiftNo = 5,1,IF(hr_att_time.ShiftNo = 6,1,0)),0),0)) AS Day20, @D21:=SUM(IF(hr_att_time.In_Status = 1 AND hr_att_time.Out_Status = 1,IF(DAY(hr_att_time.Date)='21',IF(hr_att_time.ShiftNo = 5,1,IF(hr_att_time.ShiftNo = 6,1,0)),0),0)) AS Day21, @D22:=SUM(IF(hr_att_time.In_Status = 1 AND hr_att_time.Out_Status = 1,IF(DAY(hr_att_time.Date)='22',IF(hr_att_time.ShiftNo = 5,1,IF(hr_att_time.ShiftNo = 6,1,0)),0),0)) AS Day22, @D23:=SUM(IF(hr_att_time.In_Status = 1 AND hr_att_time.Out_Status = 1,IF(DAY(hr_att_time.Date)='23',IF(hr_att_time.ShiftNo = 5,1,IF(hr_att_time.ShiftNo = 6,1,0)),0),0)) AS Day23, @D24:=SUM(IF(hr_att_time.In_Status = 1 AND hr_att_time.Out_Status = 1,IF(DAY(hr_att_time.Date)='24',IF(hr_att_time.ShiftNo = 5,1,IF(hr_att_time.ShiftNo = 6,1,0)),0),0)) AS Day24, @D25:=SUM(IF(hr_att_time.In_Status = 1 AND hr_att_time.Out_Status = 1,IF(DAY(hr_att_time.Date)='25',IF(hr_att_time.ShiftNo = 5,1,IF(hr_att_time.ShiftNo = 6,1,0)),0),0)) AS Day25, @D26:=SUM(IF(hr_att_time.In_Status = 1 AND hr_att_time.Out_Status = 1,IF(DAY(hr_att_time.Date)='26',IF(hr_att_time.ShiftNo = 5,1,IF(hr_att_time.ShiftNo = 6,1,0)),0),0)) AS Day26, @D27:=SUM(IF(hr_att_time.In_Status = 1 AND hr_att_time.Out_Status = 1,IF(DAY(hr_att_time.Date)='27',IF(hr_att_time.ShiftNo = 5,1,IF(hr_att_time.ShiftNo = 6,1,0)),0),0)) AS Day27, @D28:=SUM(IF(hr_att_time.In_Status = 1 AND hr_att_time.Out_Status = 1,IF(DAY(hr_att_time.Date)='28',IF(hr_att_time.ShiftNo = 5,1,IF(hr_att_time.ShiftNo = 6,1,0)),0),0)) AS Day28, @D29:=SUM(IF(hr_att_time.In_Status = 1 AND hr_att_time.Out_Status = 1,IF(DAY(hr_att_time.Date)='29',IF(hr_att_time.ShiftNo = 5,1,IF(hr_att_time.ShiftNo = 6,1,0)),0),0)) AS Day29, @D30:=SUM(IF(hr_att_time.In_Status = 1 AND hr_att_time.Out_Status = 1,IF(DAY(hr_att_time.Date)='30',IF(hr_att_time.ShiftNo = 5,1,IF(hr_att_time.ShiftNo = 6,1,0)),0),0)) AS Day30, @D31:=SUM(IF(hr_att_time.In_Status = 1 AND hr_att_time.Out_Status = 1,IF(DAY(hr_att_time.Date)='31',IF(hr_att_time.ShiftNo = 5,1,IF(hr_att_time.ShiftNo = 6,1,0)),0),0)) AS Day31, @TD:=(@D1+@D2+@D3+@D4+@D5+@D6+@D7+@D8+@D9+@D10+@D11+@D12+@D13+@D14+@D15+@D16+@D17+@D18+@D19+@D20+@D21+@D22+@D23+@D24+@D25+@D26+@D27+@D28+@D29+@D30+@D31) AS TotalDays, (@TD * 20)AS Amount FROM hr_att_time INNER JOIN hr_employees ON hr_att_time.EmployeeID = hr_employees.employee_id INNER JOIN hr_employee_categories ON hr_employees.category_id = hr_employee_categories.category_id LEFT JOIN hr_employee_teams ON hr_employees.employee_id = hr_employee_teams.employee_id LEFT JOIN hr_teams ON hr_employee_teams.team_id = hr_teams.team_id WHERE "; if ($category != '') { $main_query .= "hr_employees.category_id = '" . $category . "' AND "; $title .= ' / Category : ' . EmployeeCategory::find($category)->category; } if ($site != '') { $main_query .= "hr_employees.site_id = '" . $site . "' AND "; $title .= ' Site : ' . EmployeeSite::find($site)->site; } if ($department != '') { $main_query .= "hr_employees.department_id = '" . $department . "' AND "; $title .= ' / Department : ' . EmployeeDepartment::find($department)->department; } if ($section != '') { $main_query .= "hr_employees.section_id = '" . $section . "' AND "; $title .= ' / Section : ' . EmployeeSection::find($section)->section; } if ($subsection != '') { $main_query .= "hr_employees.subsection_id = '" . $subsection . "' AND "; $title .= ' / Subsection : ' . EmployeeSubsection::find($subsection)->subsection; } if ($company != '') { $main_query .= "hr_employees.company_id = '" . $company . "' AND "; $title .= ' / Company : ' . Company::find($company)->company; } if ($team != '') { $main_query .= "hr_employee_teams.team_id = '" . $team . "' AND "; $title .= ' / Team : ' . Team::find($team)->team_name; } if ($employee != '') { $main_query .= "hr_employees.employee_id = '" . $employee . "' AND "; $title .= ' / Employee : ' . Employee::find($employee)->name_withinitial; } $title = ltrim($title, ' / '); $main_query .= " hr_att_time.Date BETWEEN '" . $start_date . "' AND '" . $end_date . "' AND (hr_employees.employeestatus_id = '2' || hr_employees.employeestatus_id = '7') GROUP BY hr_att_time.EmployeeID ORDER BY convert(hr_employees.employee_id, decimal) ASC"; $night_allowance_result = DB::select($main_query); $responseBody = $this->responseBody(true, "EmployeeDetailReportController", "Report", ['body' => $night_allowance_result, 'title' => $title, 'report_date' => $start_date . " TO " . $end_date, "query" => $main_query]); } catch (Exception $exception) { $responseBody = $this->responseBody(false, "EmployeeDetailReportController", "Report", $exception); } return response()->json(["data" => $responseBody]); } public function attendanceAllowanceReport($setting, $date) { try { $title = ''; $setting_aaray = json_decode($setting); $category = $setting_aaray->category; $site = $setting_aaray->site; $department = $setting_aaray->department; $section = $setting_aaray->section; $subsection = $setting_aaray->subsection; $company = $setting_aaray->company; $team = $setting_aaray->team; $employee = $setting_aaray->employee; $query = "SELECT DISTINCT hr_salary_data.employee_id,hr_employees.name_withinitial AS employee_name,hr_employee_categories.category,hr_salary_data.worked_days, IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.earn_trans_id_01) = 'Attendance Allowance', hr_salary_data.earn_01, IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.earn_trans_id_02) = 'Attendance Allowance', hr_salary_data.earn_02, IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.earn_trans_id_03) = 'Attendance Allowance', hr_salary_data.earn_03, IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.earn_trans_id_04) = 'Attendance Allowance', hr_salary_data.earn_04, IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.earn_trans_id_05) = 'Attendance Allowance', hr_salary_data.earn_05, IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.earn_trans_id_06) = 'Attendance Allowance', hr_salary_data.earn_06, IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.earn_trans_id_07) = 'Attendance Allowance', hr_salary_data.earn_07, IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.earn_trans_id_08) = 'Attendance Allowance', hr_salary_data.earn_08, IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.earn_trans_id_09) = 'Attendance Allowance', hr_salary_data.earn_09, IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.earn_trans_id_10) = 'Attendance Allowance', hr_salary_data.earn_10, 0)))))))))) AS attendance_allowance FROM hr_att_time INNER JOIN hr_att_schedules ON hr_att_time.EmployeeID = hr_att_schedules.employee_id AND hr_att_time.Date = hr_att_schedules.schedule_date_from INNER JOIN hr_salary_data ON hr_att_time.EmployeeID = hr_salary_data.employee_id INNER JOIN hr_employees ON hr_salary_data.employee_id = hr_employees.employee_id INNER JOIN hr_employee_categories ON hr_employees.category_id = hr_employee_categories.category_id INNER JOIN hr_employee_teams ON hr_employees.employee_id = hr_employee_teams.employee_id INNER JOIN hr_teams ON hr_employee_teams.team_id = hr_teams.team_id WHERE "; if ($category != '') { $query .= "hr_employees.category_id = '" . $category . "' AND "; $title = 'Category : ' . EmployeeCategory::find($category)->category; } if ($site != '') { $query .= "hr_employees.site_id = '" . $site . "' AND "; $title = 'Site : ' . EmployeeSite::find($site)->site; } if ($department != '') { $query .= "hr_employees.department_id = '" . $department . "' AND "; $title = 'Department : ' . EmployeeDepartment::find($department)->department; } if ($section != '') { $query .= "hr_employees.section_id = '" . $section . "' AND "; $title = 'Section : ' . EmployeeSection::find($section)->section; } if ($subsection != '') { $query .= "hr_employees.subsection_id = '" . $subsection . "' AND "; $title = 'Subsection : ' . EmployeeSubsection::find($subsection)->subsection; } if ($company != '') { $query .= "hr_employees.company_id = '" . $company . "' AND "; $title = 'Company : ' . Company::find($company)->company; } if ($team != '') { $query .= "hr_employee_teams.team_id = '" . $team . "' AND "; $title = 'Team : ' . Team::find($team)->team_name; } if ($employee != '') { $query .= "hr_employees.employee_id = '" . $employee . "' AND "; $title = 'Employee : ' . Employee::find($employee)->name_withinitial; } $query .= " hr_att_time.Date LIKE CONCAT(YEAR('" . $date . "'),IF(LENGTH(MONTH('" . $date . "')) =1,CONCAT('-0',MONTH('" . $date . "')),CONCAT('-',MONTH('" . $date . "'))),'%') "; //$query = preg_replace('/\W\w+\s*(\W*)$/', '$1', $query); $query .= " GROUP BY hr_employees.employee_id"; $employees = DB::select($query); $responseBody = $this->responseBody(true, "EmployeeDetailReportController", "Report", ['body' => $employees, 'title' => $title, 'report_date' => $date]); } catch (Exception $exception) { $responseBody = $this->responseBody(false, "EmployeeDetailReportController", "Report", $exception); } return response()->json(["data" => $responseBody]); } public function allDeductionsReport($setting, $date_from, $date_to) { try { $title = ''; $setting_aaray = json_decode($setting); $category = $setting_aaray->category; $site = $setting_aaray->site; $department = $setting_aaray->department; $section = $setting_aaray->section; $subsection = $setting_aaray->subsection; $company = $setting_aaray->company; $team = $setting_aaray->team; $employee = $setting_aaray->employee; $query = "SELECT hr_salary_data.employee_id,hr_employees.name_withinitial AS employee_name,@advance := hr_salary_data.advance AS advance,@loan := hr_salary_data.loan AS loan, @deduct1 := IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_01) = 'Welfare Fund', hr_salary_data.deduct_01, IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_02) = 'Welfare Fund', hr_salary_data.deduct_02, IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_03) = 'Welfare Fund', hr_salary_data.deduct_03, IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_04) = 'Welfare Fund', hr_salary_data.deduct_04, IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_05) = 'Welfare Fund', hr_salary_data.deduct_05, IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_06) = 'Welfare Fund', hr_salary_data.deduct_06, IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_07) = 'Welfare Fund', hr_salary_data.deduct_07, IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_08) = 'Welfare Fund', hr_salary_data.deduct_08, IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_09) = 'Welfare Fund', hr_salary_data.deduct_09, IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_10) = 'Welfare Fund', hr_salary_data.deduct_10, 0)))))))))) AS welfare_fund, @deduct2 := IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_01) = 'Welfare Shop Deduction', hr_salary_data.deduct_01, IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_02) = 'Welfare Shop Deduction', hr_salary_data.deduct_02, IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_03) = 'Welfare Shop Deduction', hr_salary_data.deduct_03, IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_04) = 'Welfare Shop Deduction', hr_salary_data.deduct_04, IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_05) = 'Welfare Shop Deduction', hr_salary_data.deduct_05, IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_06) = 'Welfare Shop Deduction', hr_salary_data.deduct_06, IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_07) = 'Welfare Shop Deduction', hr_salary_data.deduct_07, IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_08) = 'Welfare Shop Deduction', hr_salary_data.deduct_08, IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_09) = 'Welfare Shop Deduction', hr_salary_data.deduct_09, IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_10) = 'Welfare Shop Deduction', hr_salary_data.deduct_10, 0)))))))))) AS welfare_shop_deduction, @deduct3 := IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_01) = 'Other Deductions', hr_salary_data.deduct_01, IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_02) = 'Other Deductions', hr_salary_data.deduct_02, IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_03) = 'Other Deductions', hr_salary_data.deduct_03, IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_04) = 'Other Deductions', hr_salary_data.deduct_04, IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_05) = 'Other Deductions', hr_salary_data.deduct_05, IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_06) = 'Other Deductions', hr_salary_data.deduct_06, IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_07) = 'Other Deductions', hr_salary_data.deduct_07, IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_08) = 'Other Deductions', hr_salary_data.deduct_08, IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_09) = 'Other Deductions', hr_salary_data.deduct_09, IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_10) = 'Other Deductions', hr_salary_data.deduct_10, 0)))))))))) AS other_deduction, (@advance + @deduct1 + @deduct2 + @deduct3) AS total_deductions FROM hr_salary_data INNER JOIN hr_employees ON hr_salary_data.employee_id = hr_employees.employee_id WHERE "; if ($category != '') { $query .= "hr_employees.category_id = '" . $category . "' AND "; $title = 'Category : ' . EmployeeCategory::find($category)->category; } if ($site != '') { $query .= "hr_employees.site_id = '" . $site . "' AND "; $title = 'Site : ' . EmployeeSite::find($site)->site; } if ($department != '') { $query .= "hr_employees.department_id = '" . $department . "' AND "; $title = 'Department : ' . EmployeeDepartment::find($department)->department; } if ($section != '') { $query .= "hr_employees.section_id = '" . $section . "' AND "; $title = 'Section : ' . EmployeeSection::find($section)->section; } if ($subsection != '') { $query .= "hr_employees.subsection_id = '" . $subsection . "' AND "; $title = 'Subsection : ' . EmployeeSubsection::find($subsection)->subsection; } if ($company != '') { $query .= "hr_employees.company_id = '" . $company . "' AND "; $title = 'Company : ' . Company::find($company)->company; } if ($employee != '') { $query .= "hr_employees.employee_id = '" . $employee . "' AND "; $title = 'Employee : ' . Employee::find($employee)->name_withinitial; } $query .= " hr_salary_data.salary_year = '" . explode("-", $date_from)[0] . "' and hr_salary_data.salary_month = '" . explode("-", $date_from)[1] . "' ORDER BY convert(hr_employees.employee_id, decimal) ASC"; //$query = preg_replace('/\W\w+\s*(\W*)$/', '$1', $query); $employees = DB::select($query); $responseBody = $this->responseBody(true, "EmployeeDetailReportController", "Report", ['body' => $employees, 'title' => $title, 'report_date' => $date_from . " To " . $date_to]); } catch (Exception $exception) { $responseBody = $this->responseBody(false, "EmployeeDetailReportController", "Report", $exception); } return response()->json(["data" => $responseBody]); } public function payAdviceReport($setting) { try { $setting_aaray = json_decode($setting); $category = $setting_aaray->category; $site = $setting_aaray->site; $department = $setting_aaray->department; $section = $setting_aaray->section; $subsection = $setting_aaray->subsection; $company = $setting_aaray->company; $team = $setting_aaray->team; $employee = $setting_aaray->employee; $year = $setting_aaray->year; $month = $setting_aaray->month; $query = "SELECT hr_employees.`employee_id` AS employee_id, hr_employees.`name_withinitial` AS employees_name, hr_employees.`attendance_id` AS attendance_id FROM `hr_employees` hr_employees LEFT JOIN hr_employee_teams ON hr_employees.employee_id = hr_employee_teams.employee_id LEFT JOIN hr_teams ON hr_employee_teams.team_id = hr_teams.team_id WHERE "; if ($category != '') { $query .= "hr_employees.category_id = '" . $category . "' AND "; } if ($site != '') { $query .= "hr_employees.site_id = '" . $site . "' AND "; } if ($department != '') { $query .= "hr_employees.department_id = '" . $department . "' AND "; } if ($section != '') { $query .= "hr_employees.section_id = '" . $section . "' AND "; } if ($subsection != '') { $query .= "hr_employees.subsection_id = '" . $subsection . "' AND "; } if ($company != '') { $query .= "hr_employees.company_id = '" . $company . "' AND "; } if ($team != '') { $query .= "hr_employee_teams.team_id = '" . $team . "' AND "; } if ($employee != '') { $query .= "hr_employees.employee_id = '" . $employee . "' AND "; } $query .= " (hr_employees.employeestatus_id = '2' || hr_employees.employeestatus_id = '7') ORDER BY convert(hr_employees.employee_id, decimal) ASC"; $query = preg_replace('/\W\w+\s*(\W*)$/', '$1', $query); $employees = DB::select($query); $salary_list = array(); foreach ($employees as $employee) { array_push($salary_list, $this->getSalaryByEmployeeID($employee->employee_id, $year, $month)); } $responseBody = $this->responseBody(false, "AllSalary", "PayAdvice", $salary_list); } catch (Exception $exception) { $responseBody = $this->responseBody(false, "AllSalary", "error", $exception); } return response()->json(["data" => $responseBody, "query" => $query]); } public function salaryReport($setting) { try { $setting_aaray = json_decode($setting); $category = $setting_aaray->category; $site = $setting_aaray->site; $department = $setting_aaray->department; $section = $setting_aaray->section; $subsection = $setting_aaray->subsection; $company = $setting_aaray->company; $team = $setting_aaray->team; $employee = $setting_aaray->employee; $year = $setting_aaray->year; $month = $setting_aaray->month; /*$query = "SELECT hr_employees.employee_id,hr_employees.name_withinitial AS employee_name,hr_salary_data.*,hr_employees.epf_no FROM hr_employees INNER JOIN hr_salary_data ON hr_employees.employee_id = hr_salary_data.employee_id LEFT JOIN hr_employee_teams ON hr_employees.employee_id = hr_employee_teams.employee_id LEFT JOIN hr_teams ON hr_employee_teams.team_id = hr_teams.team_id WHERE hr_salary_data.salary_year = '" . $year . "' AND hr_salary_data.salary_month = '" . $month . "' AND ";*/ $query = "SELECT hr_employees.employee_id, hr_employees.name_withinitial AS employee_name, hr_salary_data.id, hr_salary_data.employee_id, hr_salary_data.category_id, hr_salary_data.site_id, hr_salary_data.department_id, hr_salary_data.section_id, hr_salary_data.subsection_id, hr_salary_data.company_id, hr_salary_data.basic_salary, hr_salary_data.ot_hours1, hr_salary_data.ot_rate1, hr_salary_data.ot_rate1_amount, hr_salary_data.ot_amount1, hr_salary_data.ot_hours2, hr_salary_data.ot_rate2, hr_salary_data.ot_rate2_amount, hr_salary_data.ot_amount2, hr_salary_data.ot_hours3, hr_salary_data.ot_rate3, hr_salary_data.ot_rate3_amount, hr_salary_data.ot_amount3, hr_salary_data.advance, hr_salary_data.loan, hr_salary_data.loan_interest, hr_salary_data.debtor_loan, hr_salary_data.debtor_loan_interest, hr_salary_data.worked_days, hr_salary_data.additional_worked_days, hr_salary_data.nopay_days, hr_salary_data.nopay_amount, hr_salary_data.budget1, hr_salary_data.budget2, hr_salary_data.employer_epf, hr_salary_data.employee_epf, hr_salary_data.employee_etf, hr_salary_data.tax1, hr_salary_data.tax2, hr_salary_data.fine_hours, hr_salary_data.fine_amount, hr_salary_data.earn_trans_id_01, IF(hr_salary_data.earn_trans_id_01 = 1,hr_salary_data.earn_01, IF(hr_salary_data.earn_trans_id_02 = 1,hr_salary_data.earn_02, IF(hr_salary_data.earn_trans_id_03 = 1,hr_salary_data.earn_03, IF(hr_salary_data.earn_trans_id_04 = 1,hr_salary_data.earn_04, IF(hr_salary_data.earn_trans_id_05 = 1,hr_salary_data.earn_05, IF(hr_salary_data.earn_trans_id_06 = 1,hr_salary_data.earn_06, IF(hr_salary_data.earn_trans_id_07 = 1,hr_salary_data.earn_07, IF(hr_salary_data.earn_trans_id_08 = 1,hr_salary_data.earn_08, IF(hr_salary_data.earn_trans_id_09 = 1,hr_salary_data.earn_09, IF(hr_salary_data.earn_trans_id_10 = 1,hr_salary_data.earn_10,0)))))))))) AS earn_01,/*Attendance Allowance*/ hr_salary_data.earn_trans_id_02, IF(hr_salary_data.earn_trans_id_01 = 2,hr_salary_data.earn_01, IF(hr_salary_data.earn_trans_id_02 = 2,hr_salary_data.earn_02, IF(hr_salary_data.earn_trans_id_03 = 2,hr_salary_data.earn_03, IF(hr_salary_data.earn_trans_id_04 = 2,hr_salary_data.earn_04, IF(hr_salary_data.earn_trans_id_05 = 2,hr_salary_data.earn_05, IF(hr_salary_data.earn_trans_id_06 = 2,hr_salary_data.earn_06, IF(hr_salary_data.earn_trans_id_07 = 2,hr_salary_data.earn_07, IF(hr_salary_data.earn_trans_id_08 = 2,hr_salary_data.earn_08, IF(hr_salary_data.earn_trans_id_09 = 2,hr_salary_data.earn_09, IF(hr_salary_data.earn_trans_id_10 = 2,hr_salary_data.earn_10,0)))))))))) AS earn_02,/*Travelling Allowance*/ hr_salary_data.earn_trans_id_03, IF(hr_salary_data.earn_trans_id_01 = 3,hr_salary_data.earn_01, IF(hr_salary_data.earn_trans_id_02 = 3,hr_salary_data.earn_02, IF(hr_salary_data.earn_trans_id_03 = 3,hr_salary_data.earn_03, IF(hr_salary_data.earn_trans_id_04 = 3,hr_salary_data.earn_04, IF(hr_salary_data.earn_trans_id_05 = 3,hr_salary_data.earn_05, IF(hr_salary_data.earn_trans_id_06 = 3,hr_salary_data.earn_06, IF(hr_salary_data.earn_trans_id_07 = 3,hr_salary_data.earn_07, IF(hr_salary_data.earn_trans_id_08 = 3,hr_salary_data.earn_08, IF(hr_salary_data.earn_trans_id_09 = 3,hr_salary_data.earn_09, IF(hr_salary_data.earn_trans_id_10 = 3,hr_salary_data.earn_10,0)))))))))) AS earn_03,/*Production Allowance*/ hr_salary_data.earn_trans_id_04, IF(hr_salary_data.earn_trans_id_01 = 5,hr_salary_data.earn_01, IF(hr_salary_data.earn_trans_id_02 = 5,hr_salary_data.earn_02, IF(hr_salary_data.earn_trans_id_03 = 5,hr_salary_data.earn_03, IF(hr_salary_data.earn_trans_id_04 = 5,hr_salary_data.earn_04, IF(hr_salary_data.earn_trans_id_05 = 5,hr_salary_data.earn_05, IF(hr_salary_data.earn_trans_id_06 = 5,hr_salary_data.earn_06, IF(hr_salary_data.earn_trans_id_07 = 5,hr_salary_data.earn_07, IF(hr_salary_data.earn_trans_id_08 = 5,hr_salary_data.earn_08, IF(hr_salary_data.earn_trans_id_09 = 5,hr_salary_data.earn_09, IF(hr_salary_data.earn_trans_id_10 = 5,hr_salary_data.earn_10,0)))))))))) AS earn_04,/*Night Allowance*/ hr_salary_data.earn_trans_id_05, IF(hr_salary_data.earn_trans_id_01 = 14,hr_salary_data.earn_01, IF(hr_salary_data.earn_trans_id_02 = 14,hr_salary_data.earn_02, IF(hr_salary_data.earn_trans_id_03 = 14,hr_salary_data.earn_03, IF(hr_salary_data.earn_trans_id_04 = 14,hr_salary_data.earn_04, IF(hr_salary_data.earn_trans_id_05 = 14,hr_salary_data.earn_05, IF(hr_salary_data.earn_trans_id_06 = 14,hr_salary_data.earn_06, IF(hr_salary_data.earn_trans_id_07 = 14,hr_salary_data.earn_07, IF(hr_salary_data.earn_trans_id_08 = 14,hr_salary_data.earn_08, IF(hr_salary_data.earn_trans_id_09 = 14,hr_salary_data.earn_09, IF(hr_salary_data.earn_trans_id_10 = 14,hr_salary_data.earn_10,0)))))))))) AS earn_05,/*Service Allowance*/ hr_salary_data.earn_trans_id_06, IF(hr_salary_data.earn_trans_id_01 = 16,hr_salary_data.earn_01, IF(hr_salary_data.earn_trans_id_02 = 16,hr_salary_data.earn_02, IF(hr_salary_data.earn_trans_id_03 = 16,hr_salary_data.earn_03, IF(hr_salary_data.earn_trans_id_04 = 16,hr_salary_data.earn_04, IF(hr_salary_data.earn_trans_id_05 = 16,hr_salary_data.earn_05, IF(hr_salary_data.earn_trans_id_06 = 16,hr_salary_data.earn_06, IF(hr_salary_data.earn_trans_id_07 = 16,hr_salary_data.earn_07, IF(hr_salary_data.earn_trans_id_08 = 16,hr_salary_data.earn_08, IF(hr_salary_data.earn_trans_id_09 = 16,hr_salary_data.earn_09, IF(hr_salary_data.earn_trans_id_10 = 16,hr_salary_data.earn_10,0)))))))))) AS earn_06,/*Team Leader Incentive*/ hr_salary_data.earn_trans_id_07, IF(hr_salary_data.earn_trans_id_01 = 10,hr_salary_data.earn_01, IF(hr_salary_data.earn_trans_id_02 = 10,hr_salary_data.earn_02, IF(hr_salary_data.earn_trans_id_03 = 10,hr_salary_data.earn_03, IF(hr_salary_data.earn_trans_id_04 = 10,hr_salary_data.earn_04, IF(hr_salary_data.earn_trans_id_05 = 10,hr_salary_data.earn_05, IF(hr_salary_data.earn_trans_id_06 = 10,hr_salary_data.earn_06, IF(hr_salary_data.earn_trans_id_07 = 10,hr_salary_data.earn_07, IF(hr_salary_data.earn_trans_id_08 = 10,hr_salary_data.earn_08, IF(hr_salary_data.earn_trans_id_09 = 10,hr_salary_data.earn_09, IF(hr_salary_data.earn_trans_id_10 = 10,hr_salary_data.earn_10,0)))))))))) AS earn_07,/*Other Allowance*/ hr_salary_data.earn_trans_id_08, IF(hr_salary_data.earn_trans_id_01 = 4,hr_salary_data.earn_01, IF(hr_salary_data.earn_trans_id_02 = 4,hr_salary_data.earn_02, IF(hr_salary_data.earn_trans_id_03 = 4,hr_salary_data.earn_03, IF(hr_salary_data.earn_trans_id_04 = 4,hr_salary_data.earn_04, IF(hr_salary_data.earn_trans_id_05 = 4,hr_salary_data.earn_05, IF(hr_salary_data.earn_trans_id_06 = 4,hr_salary_data.earn_06, IF(hr_salary_data.earn_trans_id_07 = 4,hr_salary_data.earn_07, IF(hr_salary_data.earn_trans_id_08 = 4,hr_salary_data.earn_08, IF(hr_salary_data.earn_trans_id_09 = 4,hr_salary_data.earn_09, IF(hr_salary_data.earn_trans_id_10 = 4,hr_salary_data.earn_10,0)))))))))) AS earn_08,/*Mobile Allowance*/ hr_salary_data.earn_trans_id_09, IF(hr_salary_data.earn_trans_id_01 = 21,hr_salary_data.earn_01, IF(hr_salary_data.earn_trans_id_02 = 21,hr_salary_data.earn_02, IF(hr_salary_data.earn_trans_id_03 = 21,hr_salary_data.earn_03, IF(hr_salary_data.earn_trans_id_04 = 21,hr_salary_data.earn_04, IF(hr_salary_data.earn_trans_id_05 = 21,hr_salary_data.earn_05, IF(hr_salary_data.earn_trans_id_06 = 21,hr_salary_data.earn_06, IF(hr_salary_data.earn_trans_id_07 = 21,hr_salary_data.earn_07, IF(hr_salary_data.earn_trans_id_08 = 21,hr_salary_data.earn_08, IF(hr_salary_data.earn_trans_id_09 = 21,hr_salary_data.earn_09, IF(hr_salary_data.earn_trans_id_10 = 21,hr_salary_data.earn_10,0)))))))))) AS earn_09,/*Batta*/ hr_salary_data.earn_trans_id_10, IF(hr_salary_data.earn_trans_id_01 = 22,hr_salary_data.earn_01, IF(hr_salary_data.earn_trans_id_02 = 22,hr_salary_data.earn_02, IF(hr_salary_data.earn_trans_id_03 = 22,hr_salary_data.earn_03, IF(hr_salary_data.earn_trans_id_04 = 22,hr_salary_data.earn_04, IF(hr_salary_data.earn_trans_id_05 = 22,hr_salary_data.earn_05, IF(hr_salary_data.earn_trans_id_06 = 22,hr_salary_data.earn_06, IF(hr_salary_data.earn_trans_id_07 = 22,hr_salary_data.earn_07, IF(hr_salary_data.earn_trans_id_08 = 22,hr_salary_data.earn_08, IF(hr_salary_data.earn_trans_id_09 = 22,hr_salary_data.earn_09, IF(hr_salary_data.earn_trans_id_10 = 22,hr_salary_data.earn_10,0)))))))))) AS earn_10,/*Attendance Allowance Basi Employee*/ hr_salary_data.deduct_trans_id_01, IF(hr_salary_data.deduct_trans_id_01 = 7,hr_salary_data.deduct_01, IF(hr_salary_data.deduct_trans_id_02 = 7,hr_salary_data.deduct_02, IF(hr_salary_data.deduct_trans_id_03 = 7,hr_salary_data.deduct_03, IF(hr_salary_data.deduct_trans_id_04 = 7,hr_salary_data.deduct_04, IF(hr_salary_data.deduct_trans_id_05 = 7,hr_salary_data.deduct_05, IF(hr_salary_data.deduct_trans_id_06 = 7,hr_salary_data.deduct_06, IF(hr_salary_data.deduct_trans_id_07 = 7,hr_salary_data.deduct_07, IF(hr_salary_data.deduct_trans_id_08 = 7,hr_salary_data.deduct_08, IF(hr_salary_data.deduct_trans_id_09 = 7,hr_salary_data.deduct_09, IF(hr_salary_data.deduct_trans_id_10 = 7,hr_salary_data.deduct_10,0)))))))))) AS deduct_01,/*Welfare Fund */ hr_salary_data.deduct_trans_id_02, IF(hr_salary_data.deduct_trans_id_01 = 8,hr_salary_data.deduct_01, IF(hr_salary_data.deduct_trans_id_02 = 8,hr_salary_data.deduct_02, IF(hr_salary_data.deduct_trans_id_03 = 8,hr_salary_data.deduct_03, IF(hr_salary_data.deduct_trans_id_04 = 8,hr_salary_data.deduct_04, IF(hr_salary_data.deduct_trans_id_05 = 8,hr_salary_data.deduct_05, IF(hr_salary_data.deduct_trans_id_06 = 8,hr_salary_data.deduct_06, IF(hr_salary_data.deduct_trans_id_07 = 8,hr_salary_data.deduct_07, IF(hr_salary_data.deduct_trans_id_08 = 8,hr_salary_data.deduct_08, IF(hr_salary_data.deduct_trans_id_09 = 8,hr_salary_data.deduct_09, IF(hr_salary_data.deduct_trans_id_10 = 8,hr_salary_data.deduct_10,0)))))))))) AS deduct_02,/*Welfare Shop Deduction*/ hr_salary_data.deduct_trans_id_03, IF(hr_salary_data.deduct_trans_id_01 = 10,hr_salary_data.deduct_01, IF(hr_salary_data.deduct_trans_id_02 = 10,hr_salary_data.deduct_02, IF(hr_salary_data.deduct_trans_id_03 = 10,hr_salary_data.deduct_03, IF(hr_salary_data.deduct_trans_id_04 = 10,hr_salary_data.deduct_04, IF(hr_salary_data.deduct_trans_id_05 = 10,hr_salary_data.deduct_05, IF(hr_salary_data.deduct_trans_id_06 = 10,hr_salary_data.deduct_06, IF(hr_salary_data.deduct_trans_id_07 = 10,hr_salary_data.deduct_07, IF(hr_salary_data.deduct_trans_id_08 = 10,hr_salary_data.deduct_08, IF(hr_salary_data.deduct_trans_id_09 = 10,hr_salary_data.deduct_09, IF(hr_salary_data.deduct_trans_id_10 = 10,hr_salary_data.deduct_10,0)))))))))) AS deduct_03,/*Other Deductions*/ hr_salary_data.deduct_trans_id_04, IF(hr_salary_data.deduct_trans_id_01 = 11,hr_salary_data.deduct_01, IF(hr_salary_data.deduct_trans_id_02 = 11,hr_salary_data.deduct_02, IF(hr_salary_data.deduct_trans_id_03 = 11,hr_salary_data.deduct_03, IF(hr_salary_data.deduct_trans_id_04 = 11,hr_salary_data.deduct_04, IF(hr_salary_data.deduct_trans_id_05 = 11,hr_salary_data.deduct_05, IF(hr_salary_data.deduct_trans_id_06 = 11,hr_salary_data.deduct_06, IF(hr_salary_data.deduct_trans_id_07 = 11,hr_salary_data.deduct_07, IF(hr_salary_data.deduct_trans_id_08 = 11,hr_salary_data.deduct_08, IF(hr_salary_data.deduct_trans_id_09 = 11,hr_salary_data.deduct_09, IF(hr_salary_data.deduct_trans_id_10 = 11,hr_salary_data.deduct_10,0)))))))))) AS deduct_04,/*Late Deduction*/ hr_salary_data.deduct_trans_id_05, IF(hr_salary_data.deduct_trans_id_01 = 19,hr_salary_data.deduct_01, IF(hr_salary_data.deduct_trans_id_02 = 19,hr_salary_data.deduct_02, IF(hr_salary_data.deduct_trans_id_03 = 19,hr_salary_data.deduct_03, IF(hr_salary_data.deduct_trans_id_04 = 19,hr_salary_data.deduct_04, IF(hr_salary_data.deduct_trans_id_05 = 19,hr_salary_data.deduct_05, IF(hr_salary_data.deduct_trans_id_06 = 19,hr_salary_data.deduct_06, IF(hr_salary_data.deduct_trans_id_07 = 19,hr_salary_data.deduct_07, IF(hr_salary_data.deduct_trans_id_08 = 19,hr_salary_data.deduct_08, IF(hr_salary_data.deduct_trans_id_09 = 19,hr_salary_data.deduct_09, IF(hr_salary_data.deduct_trans_id_10 = 19,hr_salary_data.deduct_10,0)))))))))) AS deduct_05,/*Unspecified*/ hr_salary_data.deduct_trans_id_06, IF(hr_salary_data.deduct_trans_id_01 = 19,hr_salary_data.deduct_01, IF(hr_salary_data.deduct_trans_id_02 = 19,hr_salary_data.deduct_02, IF(hr_salary_data.deduct_trans_id_03 = 19,hr_salary_data.deduct_03, IF(hr_salary_data.deduct_trans_id_04 = 19,hr_salary_data.deduct_04, IF(hr_salary_data.deduct_trans_id_05 = 19,hr_salary_data.deduct_05, IF(hr_salary_data.deduct_trans_id_06 = 19,hr_salary_data.deduct_06, IF(hr_salary_data.deduct_trans_id_07 = 19,hr_salary_data.deduct_07, IF(hr_salary_data.deduct_trans_id_08 = 19,hr_salary_data.deduct_08, IF(hr_salary_data.deduct_trans_id_09 = 19,hr_salary_data.deduct_09, IF(hr_salary_data.deduct_trans_id_10 = 19,hr_salary_data.deduct_10,0)))))))))) AS deduct_06,/*Unspecified*/ hr_salary_data.deduct_trans_id_07, IF(hr_salary_data.deduct_trans_id_01 = 19,hr_salary_data.deduct_01, IF(hr_salary_data.deduct_trans_id_02 = 19,hr_salary_data.deduct_02, IF(hr_salary_data.deduct_trans_id_03 = 19,hr_salary_data.deduct_03, IF(hr_salary_data.deduct_trans_id_04 = 19,hr_salary_data.deduct_04, IF(hr_salary_data.deduct_trans_id_05 = 19,hr_salary_data.deduct_05, IF(hr_salary_data.deduct_trans_id_06 = 19,hr_salary_data.deduct_06, IF(hr_salary_data.deduct_trans_id_07 = 19,hr_salary_data.deduct_07, IF(hr_salary_data.deduct_trans_id_08 = 19,hr_salary_data.deduct_08, IF(hr_salary_data.deduct_trans_id_09 = 19,hr_salary_data.deduct_09, IF(hr_salary_data.deduct_trans_id_10 = 19,hr_salary_data.deduct_10,0)))))))))) AS deduct_07,/*Unspecified*/ hr_salary_data.deduct_trans_id_08, IF(hr_salary_data.deduct_trans_id_01 = 17,hr_salary_data.deduct_01, IF(hr_salary_data.deduct_trans_id_02 = 17,hr_salary_data.deduct_02, IF(hr_salary_data.deduct_trans_id_03 = 17,hr_salary_data.deduct_03, IF(hr_salary_data.deduct_trans_id_04 = 17,hr_salary_data.deduct_04, IF(hr_salary_data.deduct_trans_id_05 = 17,hr_salary_data.deduct_05, IF(hr_salary_data.deduct_trans_id_06 = 17,hr_salary_data.deduct_06, IF(hr_salary_data.deduct_trans_id_07 = 17,hr_salary_data.deduct_07, IF(hr_salary_data.deduct_trans_id_08 = 17,hr_salary_data.deduct_08, IF(hr_salary_data.deduct_trans_id_09 = 17,hr_salary_data.deduct_09, IF(hr_salary_data.deduct_trans_id_10 = 17,hr_salary_data.deduct_10,0)))))))))) AS deduct_08,/*Advance*/ hr_salary_data.deduct_trans_id_09, IF(hr_salary_data.deduct_trans_id_01 = 23,hr_salary_data.deduct_01, IF(hr_salary_data.deduct_trans_id_02 = 23,hr_salary_data.deduct_02, IF(hr_salary_data.deduct_trans_id_03 = 23,hr_salary_data.deduct_03, IF(hr_salary_data.deduct_trans_id_04 = 23,hr_salary_data.deduct_04, IF(hr_salary_data.deduct_trans_id_05 = 23,hr_salary_data.deduct_05, IF(hr_salary_data.deduct_trans_id_06 = 23,hr_salary_data.deduct_06, IF(hr_salary_data.deduct_trans_id_07 = 23,hr_salary_data.deduct_07, IF(hr_salary_data.deduct_trans_id_08 = 23,hr_salary_data.deduct_08, IF(hr_salary_data.deduct_trans_id_09 = 23,hr_salary_data.deduct_09, IF(hr_salary_data.deduct_trans_id_10 = 23,hr_salary_data.deduct_10,0)))))))))) AS deduct_09,/*Festival Advace*/ hr_salary_data.deduct_trans_id_10, IF(hr_salary_data.deduct_trans_id_01 = 26,hr_salary_data.deduct_01, IF(hr_salary_data.deduct_trans_id_02 = 26,hr_salary_data.deduct_02, IF(hr_salary_data.deduct_trans_id_03 = 26,hr_salary_data.deduct_03, IF(hr_salary_data.deduct_trans_id_04 = 26,hr_salary_data.deduct_04, IF(hr_salary_data.deduct_trans_id_05 = 26,hr_salary_data.deduct_05, IF(hr_salary_data.deduct_trans_id_06 = 26,hr_salary_data.deduct_06, IF(hr_salary_data.deduct_trans_id_07 = 26,hr_salary_data.deduct_07, IF(hr_salary_data.deduct_trans_id_08 = 26,hr_salary_data.deduct_08, IF(hr_salary_data.deduct_trans_id_09 = 26,hr_salary_data.deduct_09, IF(hr_salary_data.deduct_trans_id_10 = 26,hr_salary_data.deduct_10,0)))))))))) AS deduct_10,/*Meal Deduction*/ hr_salary_data.last_adjustment, hr_salary_data.this_adjustment, hr_salary_data.gross_salary, hr_salary_data.total_deduction, hr_salary_data.net_salary, hr_salary_data.salary_month, hr_salary_data.salary_year, hr_salary_data.created_at, hr_salary_data.updated_at FROM hr_employees INNER JOIN hr_salary_data ON hr_employees.employee_id = hr_salary_data.employee_id LEFT JOIN hr_employee_teams ON hr_employees.employee_id = hr_employee_teams.employee_id LEFT JOIN hr_teams ON hr_employee_teams.team_id = hr_teams.team_id WHERE hr_salary_data.salary_year = '" . $year . "' AND hr_salary_data.salary_month = '" . $month . "' AND "; if ($category != '') { $query .= "hr_employees.category_id = '" . $category . "' AND "; } if ($site != '') { $query .= "hr_employees.site_id = '" . $site . "' AND "; } if ($department != '') { $query .= "hr_employees.department_id = '" . $department . "' AND "; } if ($section != '') { $query .= "hr_employees.section_id = '" . $section . "' AND "; } if ($subsection != '') { $query .= "hr_employees.subsection_id = '" . $subsection . "' AND "; } if ($company != '') { $query .= "hr_employees.company_id = '" . $company . "' AND "; } if ($team != '') { $query .= "hr_employee_teams.team_id = '" . $team . "' AND "; } if ($employee != '') { $query .= "hr_employees.employee_id = '" . $employee . "' AND "; } $query .= " (hr_employees.employeestatus_id = '2' || hr_employees.employeestatus_id = '7') ORDER BY convert(hr_employees.employee_id, decimal) ASC"; $query = preg_replace('/\W\w+\s*(\W*)$/', '$1', $query); $employees = DB::select($query); $earning_narations = DB::select("SELECT hr_transaction_items.narration FROM hr_transaction_items INNER JOIN hr_transaction_item_setups ON hr_transaction_items.item_id = hr_transaction_item_setups.item_id WHERE hr_transaction_items.type = '0'"); $deduction_narations = DB::select("SELECT hr_transaction_items.narration FROM hr_transaction_items INNER JOIN hr_transaction_item_setups ON hr_transaction_items.item_id = hr_transaction_item_setups.item_id WHERE hr_transaction_items.type = '1'"); //$this->getBasicSalary($employees); //$this->getSalaryData($employees, $year, $month); $responseBody = $this->responseBody(true, "AllSalary", "PayAdvice", ["result" => $employees, "earning_narration" => $earning_narations, "deduction_narration" => $deduction_narations]); } catch (Exception $exception) { $responseBody = $this->responseBody(false, "AllSalary", "PayAdvice", $exception); } return response()->json(["data" => $responseBody]); } private function getBasicSalary($employees) { foreach ($employees as $employee) { $basic_salary = EmployeeSalary::where('employee_id', '=', $employee->employee_id)->first(); if ($basic_salary) { $employee->basic_salary = $basic_salary->salary; } else { $employee->basic_salary = 0.00; } } } private function getSalaryData($employees, $year, $month) { foreach ($employees as $employee) { $salary_data = SalaryData::where([['employee_id', '=', $employee->employee_id], ['salary_month', '=', $month], ['salary_year', '=', $year]])->first(); if ($salary_data) { $employee->budget_allowance1 = $salary_data->budget1; $employee->budget_allowance2 = $salary_data->budget2; $employee->work_days = $salary_data->worked_days; $employee->nopay_days = $salary_data->nopay_days; $employee->basic_earning = $salary_data->basic_salary; $employee->ot_hours1 = $salary_data->ot_hours1; $employee->ot_amount1 = $salary_data->ot_amount1; $employee->ot_hours2 = $salary_data->ot_hours2; $employee->ot_amount2 = $salary_data->ot_amount2; $employee->ot_hours3 = $salary_data->ot_hours3; $employee->ot_amount3 = $salary_data->ot_amount3; $employee->total_ot = ($salary_data->ot_amount1 + $salary_data->ot_amount2 + $salary_data->ot_amount3); $employee->earn1 = $this->earningNaration($salary_data->earn_trans_id_01); $employee->earn_amount1 = $salary_data->earn_01; $employee->earn2 = $this->earningNaration($salary_data->earn_trans_id_02); $employee->earn_amount2 = $salary_data->earn_02; $employee->earn3 = $this->earningNaration($salary_data->earn_trans_id_03); $employee->earn_amount3 = $salary_data->earn_03; $employee->earn4 = $this->earningNaration($salary_data->earn_trans_id_04); $employee->earn_amount4 = $salary_data->earn_04; $employee->earn5 = $this->earningNaration($salary_data->earn_trans_id_05); $employee->earn_amount5 = $salary_data->earn_05; $employee->earn6 = $this->earningNaration($salary_data->earn_trans_id_06); $employee->earn_amount6 = $salary_data->earn_06; $employee->earn7 = $this->earningNaration($salary_data->earn_trans_id_07); $employee->earn_amount7 = $salary_data->earn_07; $employee->earn8 = $this->earningNaration($salary_data->earn_trans_id_08); $employee->earn_amount8 = $salary_data->earn_08; $employee->earn9 = $this->earningNaration($salary_data->earn_trans_id_09); $employee->earn_amount9 = $salary_data->earn_09; $employee->earn10 = $this->earningNaration($salary_data->earn_trans_id_10); $employee->earn_amount10 = $salary_data->earn_10; $employee->deduct1 = $this->deductionNaration($salary_data->deduct_trans_id_01); $employee->deduct_amount1 = $salary_data->deduct_01; $employee->deduct2 = $this->deductionNaration($salary_data->deduct_trans_id_02); $employee->deduct_amount2 = $salary_data->deduct_02; $employee->deduct3 = $this->deductionNaration($salary_data->deduct_trans_id_03); $employee->deduct_amount3 = $salary_data->deduct_03; $employee->deduct4 = $this->deductionNaration($salary_data->deduct_trans_id_04); $employee->deduct_amount4 = $salary_data->deduct_04; $employee->deduct5 = $this->deductionNaration($salary_data->deduct_trans_id_05); $employee->deduct_amount5 = $salary_data->deduct_05; $employee->deduct6 = $this->deductionNaration($salary_data->deduct_trans_id_06); $employee->deduct_amount6 = $salary_data->deduct_06; $employee->deduct7 = $this->deductionNaration($salary_data->deduct_trans_id_07); $employee->deduct_amount7 = $salary_data->deduct_07; $employee->deduct8 = $this->deductionNaration($salary_data->deduct_trans_id_08); $employee->deduct_amount8 = $salary_data->deduct_08; $employee->deduct9 = $this->deductionNaration($salary_data->deduct_trans_id_09); $employee->deduct_amount9 = $salary_data->deduct_09; $employee->deduct10 = $this->deductionNaration($salary_data->deduct_trans_id_10); $employee->deduct_amount10 = $salary_data->deduct_10; $employee->gross_salary = $salary_data->gross_salary; $employee->epf = $salary_data->employee_epf; $employee->advance = $salary_data->advance; $employee->loan = $salary_data->loan; $employee->debtor_loan = $salary_data->debtor_loan; $employee->fine_amount = $salary_data->fine_amount; $employee->total_deduction = $salary_data->total_deduction; $employee->net_salary = $salary_data->net_salary; } else { $employee->basic_salary = 0.00; $employee->budget_allowance1 = 0.00; $employee->budget_allowance2 = 0.00; $employee->work_days = 0; $employee->nopay_days = 0; $employee->basic_earning = 0.0; $employee->ot_hours1 = 0; $employee->ot_amount1 = 0.00; $employee->ot_hours2 = 0; $employee->ot_amount2 = 0.0; $employee->ot_hours3 = 0; $employee->ot_amount3 = 0.0; $employee->total_ot = 0.0; $employee->earn1 = ""; $employee->earn_amount1 = 0.0; $employee->earn2 = ""; $employee->earn_amount2 = 0.0; $employee->earn3 = ""; $employee->earn_amount3 = 0.0; $employee->earn4 = ""; $employee->earn_amount4 = 0.0; $employee->earn5 = ""; $employee->earn_amount5 = 0.0; $employee->earn6 = ""; $employee->earn_amount6 = 0.0; $employee->earn7 = ""; $employee->earn_amount7 = 0.0; $employee->earn8 = ""; $employee->earn_amount8 = 0.0; $employee->earn9 = ""; $employee->earn_amount9 = 0.0; $employee->earn10 = ""; $employee->earn_amount10 = 0.0; $employee->deduct1 = ""; $employee->deduct_amount1 = 0.0; $employee->deduct2 = ""; $employee->deduct_amount2 = 0.0; $employee->deduct3 = ""; $employee->deduct_amount3 = 0.0; $employee->deduct4 = ""; $employee->deduct_amount4 = 0.0; $employee->deduct5 = ""; $employee->deduct_amount5 = 0.0; $employee->deduct6 = ""; $employee->deduct_amount6 = 0.0; $employee->deduct7 = ""; $employee->deduct_amount7 = 0.0; $employee->deduct8 = ""; $employee->deduct_amount8 = 0.0; $employee->deduct9 = ""; $employee->deduct_amount9 = 0.0; $employee->deduct10 = ""; $employee->deduct_amount10 = 0.0; $employee->gross_salary = 0.0; $employee->epf = 0.0; $employee->advance = 0.0; $employee->loan = 0.0; $employee->debtor_loan = 0.0; $employee->fine_amount = 0.0; $employee->total_deduction = 0.0; $employee->net_salary = 0.0; } } } private function earningNaration($id) { $earning = OtherEarningDeduction::where('item_id', '=', $id)->first(); if ($earning) { if ($earning->type == 1) { return $earning->title; } } return ""; } private function deductionNaration($id) { $deduction = OtherEarningDeduction::where('item_id', '=', $id)->first(); if ($deduction) { if ($deduction->type == 3) { return $deduction->title; } } return ""; } public function employeeDailyOTReport($setting, $year, $month) { try { $date = $year . "-" . $month . "-01"; $title = ''; $setting_aaray = json_decode($setting); $category = $setting_aaray->category; $site = $setting_aaray->site; $department = $setting_aaray->department; $section = $setting_aaray->section; $subsection = $setting_aaray->subsection; $company = $setting_aaray->company; $team = $setting_aaray->team; $employee = $setting_aaray->employee; ini_set('max_execution_time', '0'); // for infinite time of execution $query = "SELECT hr_employees.employee_id, hr_employees.name_withinitial AS employee_name, SUM(CASE WHEN DAY(hr_att_time.Date) = 1 THEN hr_att_time.N_overtime + hr_att_time.D_overtime ELSE 0 END) AS 'day1', SUM(CASE WHEN DAY(hr_att_time.Date) = 2 THEN hr_att_time.N_overtime + hr_att_time.D_overtime ELSE 0 END) AS 'day2', SUM(CASE WHEN DAY(hr_att_time.Date) = 3 THEN hr_att_time.N_overtime + hr_att_time.D_overtime ELSE 0 END) AS 'day3', SUM(CASE WHEN DAY(hr_att_time.Date) = 4 THEN hr_att_time.N_overtime + hr_att_time.D_overtime ELSE 0 END) AS 'day4', SUM(CASE WHEN DAY(hr_att_time.Date) = 5 THEN hr_att_time.N_overtime + hr_att_time.D_overtime ELSE 0 END) AS 'day5', SUM(CASE WHEN DAY(hr_att_time.Date) = 6 THEN hr_att_time.N_overtime + hr_att_time.D_overtime ELSE 0 END) AS 'day6', SUM(CASE WHEN DAY(hr_att_time.Date) = 7 THEN hr_att_time.N_overtime + hr_att_time.D_overtime ELSE 0 END) AS 'day7', SUM(CASE WHEN DAY(hr_att_time.Date) = 8 THEN hr_att_time.N_overtime + hr_att_time.D_overtime ELSE 0 END) AS 'day8', SUM(CASE WHEN DAY(hr_att_time.Date) = 9 THEN hr_att_time.N_overtime + hr_att_time.D_overtime ELSE 0 END) AS 'day9', SUM(CASE WHEN DAY(hr_att_time.Date) = 10 THEN hr_att_time.N_overtime + hr_att_time.D_overtime ELSE 0 END) AS 'day10', SUM(CASE WHEN DAY(hr_att_time.Date) = 11 THEN hr_att_time.N_overtime + hr_att_time.D_overtime ELSE 0 END) AS 'day11', SUM(CASE WHEN DAY(hr_att_time.Date) = 12 THEN hr_att_time.N_overtime + hr_att_time.D_overtime ELSE 0 END) AS 'day12', SUM(CASE WHEN DAY(hr_att_time.Date) = 13 THEN hr_att_time.N_overtime + hr_att_time.D_overtime ELSE 0 END) AS 'day13', SUM(CASE WHEN DAY(hr_att_time.Date) = 14 THEN hr_att_time.N_overtime + hr_att_time.D_overtime ELSE 0 END) AS 'day14', SUM(CASE WHEN DAY(hr_att_time.Date) = 15 THEN hr_att_time.N_overtime + hr_att_time.D_overtime ELSE 0 END) AS 'day15', SUM(CASE WHEN DAY(hr_att_time.Date) = 16 THEN hr_att_time.N_overtime + hr_att_time.D_overtime ELSE 0 END) AS 'day16', SUM(CASE WHEN DAY(hr_att_time.Date) = 17 THEN hr_att_time.N_overtime + hr_att_time.D_overtime ELSE 0 END) AS 'day17', SUM(CASE WHEN DAY(hr_att_time.Date) = 18 THEN hr_att_time.N_overtime + hr_att_time.D_overtime ELSE 0 END) AS 'day18', SUM(CASE WHEN DAY(hr_att_time.Date) = 19 THEN hr_att_time.N_overtime + hr_att_time.D_overtime ELSE 0 END) AS 'day19', SUM(CASE WHEN DAY(hr_att_time.Date) = 20 THEN hr_att_time.N_overtime + hr_att_time.D_overtime ELSE 0 END) AS 'day20', SUM(CASE WHEN DAY(hr_att_time.Date) = 21 THEN hr_att_time.N_overtime + hr_att_time.D_overtime ELSE 0 END) AS 'day21', SUM(CASE WHEN DAY(hr_att_time.Date) = 22 THEN hr_att_time.N_overtime + hr_att_time.D_overtime ELSE 0 END) AS 'day22', SUM(CASE WHEN DAY(hr_att_time.Date) = 23 THEN hr_att_time.N_overtime + hr_att_time.D_overtime ELSE 0 END) AS 'day23', SUM(CASE WHEN DAY(hr_att_time.Date) = 24 THEN hr_att_time.N_overtime + hr_att_time.D_overtime ELSE 0 END) AS 'day24', SUM(CASE WHEN DAY(hr_att_time.Date) = 25 THEN hr_att_time.N_overtime + hr_att_time.D_overtime ELSE 0 END) AS 'day25', SUM(CASE WHEN DAY(hr_att_time.Date) = 26 THEN hr_att_time.N_overtime + hr_att_time.D_overtime ELSE 0 END) AS 'day26', SUM(CASE WHEN DAY(hr_att_time.Date) = 27 THEN hr_att_time.N_overtime + hr_att_time.D_overtime ELSE 0 END) AS 'day27', SUM(CASE WHEN DAY(hr_att_time.Date) = 28 THEN hr_att_time.N_overtime + hr_att_time.D_overtime ELSE 0 END) AS 'day28', SUM(CASE WHEN DAY(hr_att_time.Date) = 29 THEN hr_att_time.N_overtime + hr_att_time.D_overtime ELSE 0 END) AS 'day29', SUM(CASE WHEN DAY(hr_att_time.Date) = 30 THEN hr_att_time.N_overtime + hr_att_time.D_overtime ELSE 0 END) AS 'day30', SUM(CASE WHEN DAY(hr_att_time.Date) = 31 THEN hr_att_time.N_overtime + hr_att_time.D_overtime ELSE 0 END) AS 'day31', SUM(hr_att_time.N_overtime + hr_att_time.D_overtime) AS TotalOTHours, SUM(IF(hr_att_time.In_Status = 1,1,0)) AS present_day, (hr_employee_categories.work_days_month - SUM(hr_att_time.Worked_Day)) AS absent_day, 0 AS leave_day, (SUM(hr_att_time.Worked_Day)+(hr_employee_categories.work_days_month - SUM(hr_att_time.Worked_Day)) ) AS total_day FROM hr_att_time INNER JOIN hr_employees ON hr_att_time.EmployeeID = hr_employees.employee_id INNER JOIN hr_att_schedules ON hr_att_time.EmployeeID = hr_att_schedules.employee_id AND hr_att_time.Date = hr_att_schedules.schedule_date_from INNER JOIN hr_employee_categories ON hr_employees.category_id = hr_employee_categories.category_id LEFT JOIN hr_employee_teams ON hr_employees.employee_id = hr_employee_teams.employee_id INNER JOIN hr_teams ON hr_employee_teams.team_id = hr_teams.team_id WHERE "; if ($category != '') { $query .= "hr_employees.category_id = '" . $category . "' AND "; $title = 'Category : ' . EmployeeCategory::find($category)->category; } if ($site != '') { $query .= "hr_employees.site_id = '" . $site . "' AND "; $title = 'Site : ' . EmployeeSite::find($site)->site; } if ($department != '') { $query .= "hr_employees.department_id = '" . $department . "' AND "; $title = 'Department : ' . EmployeeDepartment::find($department)->department; } if ($section != '') { $query .= "hr_employees.section_id = '" . $section . "' AND "; $title = 'Section : ' . EmployeeSection::find($section)->section; } if ($subsection != '') { $query .= "hr_employees.subsection_id = '" . $subsection . "' AND "; $title = 'Subsection : ' . EmployeeSubsection::find($subsection)->subsection; } if ($company != '') { $query .= "hr_employees.company_id = '" . $company . "' AND "; $title = 'Company : ' . Company::find($company)->company; } if ($team != '') { $query .= "hr_employee_teams.team_id = '" . $team . "' AND "; $title = 'Team : ' . Team::find($team)->team_name; } if ($employee != '') { $query .= "hr_employees.employee_id = '" . $employee . "' AND "; $title = 'Employee : ' . Employee::find($employee)->name_withinitial; } $query .= " hr_att_time.Date LIKE CONCAT(YEAR('" . $date . "'),IF(CHAR_LENGTH(MONTH('" . $date . "')) = 1,CONCAT('-0',MONTH('" . $date . "')),CONCAT('-',MONTH('" . $date . "'))),'%') AND hr_employees.employeestatus_id != '4' GROUP BY hr_att_time.EmployeeID ORDER BY convert(hr_employees.employee_id, decimal) ASC"; $employees = DB::select($query); $date = DB::select("SELECT YEAR('" . $date . "') AS year, MONTHNAME('" . $date . "') AS month"); $responseBody = $this->responseBody(true, "EmployeeDetailReportController", "Report", ['body' => $employees, 'title' => $title, "report_date" => $date]); } catch (Exception $exception) { $responseBody = $this->responseBody(false, "EmployeeDetailReportController", "Report", $exception); } return response()->json(["data" => $responseBody]); } private function allEarningDeduction() { } /** * getEmployeeByID * This function is used to search employee by employee_id. * @param id This is the paramter for search employee. */ public function getSalaryByEmployeeID($id, $year, $month) { $employee = $this->employee($id, $month); $salary_data = $this->getCurrentMonthSalaryData($id, $year, $month); if ($salary_data) { $this->allTransactionItemEarningsByEmployee($salary_data); $this->allTransactionItemDeductionsByEmployee($salary_data); } else { $this->allEarnings($employee); $this->allDeductions($employee); } return ["employee" => $employee, "current_salary" => $salary_data]; } /** * employee * This function is used to search employee with settings. * @param id This is the paramter for search employee. */ private function employee($id, $month) { try { $employee = Employee::find($id); $category = EmployeeCategory::find($employee->category_id); $employee->category = $category->category; $designation = EmployeeDesignation::find($employee->designation_id); $employee->designation = $designation->designation; $employee->designation_sinhala = $designation->designation_sinhala; $site = EmployeeSite::find($employee->site_id); $employee->site = $site->site; $department = EmployeeDepartment::find($employee->department_id); $employee->department = $department->department; $section = EmployeeSection::find($employee->section_id); $employee->section = $section->section; $sub_section = EmployeeSubsection::find($employee->subsection_id); $employee->subsection = $sub_section->subsection; $company = Company::find($employee->company_id); $employee->company = $company->company; $employee->basicSalary = 0.0; $salary = EmployeeSalary::where('employee_id', '=', $id)->where('status', '=', '1')->first(); if ($salary != null) { $employee->basicSalary = $salary->salary; } $category = EmployeeCategory::find($employee->category_id); if ($month == 2) { if ($category->work_days_month > 0) { $category->work_days_month -= 1; } } $employee->category = $category; return $employee; } catch (\Exception $exception) { return $exception; } } /** * getCurrentMonthSalaryData * This function is used to get current month salary sheet. * @param request This is the paramter for salary sheet. * @param id This is the paramter for salary sheet. */ private function getCurrentMonthSalaryData($id, $year, $month) { $salaryData = SalaryData::where('employee_id', '=', $id)->where('salary_year', '=', $year)->where('salary_month', '=', $month)->first(); if ($salaryData) { $salaryData->employee_basic_salary = 0; $query = 'SELECT SUM(hr_att_time.Worked_Day) AS employee_worked_day,SUM(hr_att_time.NoPay) AS employee_nopay_day FROM hr_att_time WHERE hr_att_time.EmployeeID = "' . $id . '" AND YEAR(hr_att_time.Date) = "' . $year . '" AND MONTH(hr_att_time.Date) = "' . $month . '"'; $result = DB::select($query); if ($result) { $salaryData->real_work_day = $result[0]->employee_worked_day; $salaryData->real_nopay_day = $result[0]->employee_nopay_day; } $salary = EmployeeSalary::where('employee_id', '=', $id)->first(); if ($salary) { $salaryData->employee_basic_salary = $salary->salary; } } return $salaryData; } /** * allTransactionItemEarningsByEmployee * This function is used to search employee's earnings. * @param id This is the paramter for search employee's earnings. */ private function allTransactionItemEarningsByEmployee($salary) { $id1 = $salary->earn_trans_id_01; $id2 = $salary->earn_trans_id_02; $id3 = $salary->earn_trans_id_03; $id4 = $salary->earn_trans_id_04; $id5 = $salary->earn_trans_id_05; $id6 = $salary->earn_trans_id_06; $id7 = $salary->earn_trans_id_07; $id8 = $salary->earn_trans_id_08; $id9 = $salary->earn_trans_id_09; $id10 = $salary->earn_trans_id_10; $amount1 = $salary->earn_01; $amount2 = $salary->earn_02; $amount3 = $salary->earn_03; $amount4 = $salary->earn_04; $amount5 = $salary->earn_05; $amount6 = $salary->earn_06; $amount7 = $salary->earn_07; $amount8 = $salary->earn_08; $amount9 = $salary->earn_09; $amount10 = $salary->earn_10; if ($id1 != 0) { $name = TransactionItem::where('item_id', '=', $id1)->first()->narration; $name_sinhala = TransactionItem::where('item_id', '=', $id1)->first()->narration_sinhala; $salary->earn_trans_id_01 = [$id1, $name, $amount1, $name_sinhala]; $salary->earn_01 = $amount1; } if ($id2 != 0) { $name = TransactionItem::where('item_id', '=', $id2)->first()->narration; $name_sinhala = TransactionItem::where('item_id', '=', $id2)->first()->narration_sinhala; $salary->earn_trans_id_02 = [$id2, $name, $amount2, $name_sinhala]; $salary->earn_02 = $amount2; } if ($id3 != 0) { $name = TransactionItem::where('item_id', '=', $id3)->first()->narration; $name_sinhala = TransactionItem::where('item_id', '=', $id3)->first()->narration_sinhala; $salary->earn_trans_id_03 = [$id3, $name, $amount3, $name_sinhala]; $salary->earn_03 = $amount3; } if ($id4 != 0) { $name = TransactionItem::where('item_id', '=', $id4)->first()->narration; $name_sinhala = TransactionItem::where('item_id', '=', $id4)->first()->narration_sinhala; $salary->earn_trans_id_04 = [$id4, $name, $amount4, $name_sinhala]; $salary->earn_04 = $amount4; } if ($id5 != 0) { $name = TransactionItem::where('item_id', '=', $id5)->first()->narration; $name_sinhala = TransactionItem::where('item_id', '=', $id5)->first()->narration_sinhala; $salary->earn_trans_id_05 = [$id5, $name, $amount5, $name_sinhala]; $salary->earn_05 = $amount5; } if ($id6 != 0) { $name = TransactionItem::where('item_id', '=', $id6)->first()->narration; $name_sinhala = TransactionItem::where('item_id', '=', $id6)->first()->narration_sinhala; $salary->earn_trans_id_06 = [$id6, $name, $amount6, $name_sinhala]; $salary->earn_06 = $amount6; } if ($id7 != 0) { $name = TransactionItem::where('item_id', '=', $id7)->first()->narration; $name_sinhala = TransactionItem::where('item_id', '=', $id7)->first()->narration_sinhala; $salary->earn_trans_id_07 = [$id7, $name, $amount7, $name_sinhala]; $salary->earn_07 = $amount7; } if ($id8 != 0) { $name = TransactionItem::where('item_id', '=', $id8)->first()->narration; $name_sinhala = TransactionItem::where('item_id', '=', $id8)->first()->narration_sinhala; $salary->earn_trans_id_08 = [$id8, $name, $amount8, $name_sinhala]; $salary->earn_08 = $amount8; } if ($id9 != 0) { $name = TransactionItem::where('item_id', '=', $id9)->first()->narration; $name_sinhala = TransactionItem::where('item_id', '=', $id9)->first()->narration_sinhala; $salary->earn_trans_id_09 = [$id9, $name, $amount9, $name_sinhala]; $salary->earn_09 = $amount9; } if ($id10 != 0) { $name = TransactionItem::where('item_id', '=', $id10)->first()->narration; $name_sinhala = TransactionItem::where('item_id', '=', $id10)->first()->narration_sinhala; $salary->earn_trans_id_10 = [$id10, $name, $amount10, $name_sinhala]; $salary->earn_10 = $amount10; } } /** * allTransactionItemDeductionsByEmployee * This function is used to search employee's deductions. * @param id This is the paramter for search employee's deductions. */ private function allTransactionItemDeductionsByEmployee($salary) { $id1 = $salary->deduct_trans_id_01; $id2 = $salary->deduct_trans_id_02; $id3 = $salary->deduct_trans_id_03; $id4 = $salary->deduct_trans_id_04; $id5 = $salary->deduct_trans_id_05; $id6 = $salary->deduct_trans_id_06; $id7 = $salary->deduct_trans_id_07; $id8 = $salary->deduct_trans_id_08; $id9 = $salary->deduct_trans_id_09; $id10 = $salary->deduct_trans_id_10; $amount1 = $salary->deduct_01; $amount2 = $salary->deduct_02; $amount3 = $salary->deduct_03; $amount4 = $salary->deduct_04; $amount5 = $salary->deduct_05; $amount6 = $salary->deduct_06; $amount7 = $salary->deduct_07; $amount8 = $salary->deduct_08; $amount9 = $salary->deduct_09; $amount10 = $salary->deduct_10; if ($id1 != 0) { $name = TransactionItem::where('item_id', '=', $id1)->first()->narration; $name_sinhala = TransactionItem::where('item_id', '=', $id1)->first()->narration_sinhala; $salary->deduct_trans_id_01 = [$id1, $name, $amount1, $name_sinhala]; $salary->deduct_01 = $amount1; } if ($id2 != 0) { $name = TransactionItem::where('item_id', '=', $id2)->first()->narration; $name_sinhala = TransactionItem::where('item_id', '=', $id2)->first()->narration_sinhala; $salary->deduct_trans_id_02 = [$id2, $name, $amount2, $name_sinhala]; $salary->deduct_02 = $amount2; } if ($id3 != 0) { $name = TransactionItem::where('item_id', '=', $id3)->first()->narration; $name_sinhala = TransactionItem::where('item_id', '=', $id3)->first()->narration_sinhala; $salary->deduct_trans_id_03 = [$id3, $name, $amount3, $name_sinhala]; $salary->deduct_03 = $amount3; } if ($id4 != 0) { $name = TransactionItem::where('item_id', '=', $id4)->first()->narration; $name_sinhala = TransactionItem::where('item_id', '=', $id4)->first()->narration_sinhala; $salary->deduct_trans_id_04 = [$id4, $name, $amount4, $name_sinhala]; $salary->deduct_04 = $amount4; } if ($id5 != 0) { $name = TransactionItem::where('item_id', '=', $id5)->first()->narration; $name_sinhala = TransactionItem::where('item_id', '=', $id5)->first()->narration_sinhala; $salary->deduct_trans_id_05 = [$id5, $name, $amount5, $name_sinhala]; $salary->deduct_05 = $amount5; } if ($id6 != 0) { $name = TransactionItem::where('item_id', '=', $id6)->first()->narration; $name_sinhala = TransactionItem::where('item_id', '=', $id6)->first()->narration_sinhala; $salary->deduct_trans_id_06 = [$id6, $name, $amount6, $name_sinhala]; $salary->deduct_06 = $amount6; } if ($id7 != 0) { $name = TransactionItem::where('item_id', '=', $id7)->first()->narration; $name_sinhala = TransactionItem::where('item_id', '=', $id7)->first()->narration_sinhala; $salary->deduct_trans_id_07 = [$id7, $name, $amount7, $name_sinhala]; $salary->deduct_07 = $amount7; } if ($id8 != 0) { $name = TransactionItem::where('item_id', '=', $id8)->first()->narration; $name_sinhala = TransactionItem::where('item_id', '=', $id8)->first()->narration_sinhala; $salary->deduct_trans_id_08 = [$id8, $name, $amount8, $name_sinhala]; $salary->deduct_08 = $amount8; } if ($id9 != 0) { $name = TransactionItem::where('item_id', '=', $id9)->first()->narration; $name_sinhala = TransactionItem::where('item_id', '=', $id9)->first()->narration_sinhala; $salary->deduct_trans_id_09 = [$id9, $name, $amount9, $name_sinhala]; $salary->deduct_09 = $amount9; } if ($id10 != 0) { $name = TransactionItem::where('item_id', '=', $id10)->first()->narration; $name_sinhala = TransactionItem::where('item_id', '=', $id10)->first()->narration_sinhala; $salary->deduct_trans_id_10 = [$id10, $name, $amount10, $name_sinhala]; $salary->deduct_10 = $amount10; } } private function allEarnings($employee) { $query = "SELECT hr_transaction_item_setups.`narration` AS narration, hr_transaction_item_setups.`item_id` As item_id, hr_other_earning_deduction.`type` AS type, hr_other_earning_deduction.`formula` AS formula, hr_transaction_item_setups.`status` AS hr_transaction_item_setups_status FROM `hr_transaction_items` hr_transaction_items INNER JOIN `hr_other_earning_deduction` hr_other_earning_deduction ON hr_transaction_items.`item_id` = hr_other_earning_deduction.`item_id` INNER JOIN `hr_transaction_item_setups` hr_transaction_item_setups ON hr_transaction_items.`item_id` = hr_transaction_item_setups.`item_id` WHERE hr_other_earning_deduction.type = '1' AND hr_transaction_item_setups.status = '1'"; $result = DB::select($query); if (count($result) > 0) { $employee->earn_trans_id_01 = [$result[0]->item_id, $result[0]->narration, $result[0]->formula]; $employee->earn_01 = $result[0]->formula; } else { $employee->earn_trans_id_01 = 0; $employee->earn_01 = 0; } if (count($result) > 1) { $employee->earn_trans_id_02 = [$result[1]->item_id, $result[1]->narration, $result[1]->formula]; $employee->earn_02 = $result[1]->formula; } else { $employee->earn_trans_id_02 = 0; $employee->earn_02 = 0; } if (count($result) > 2) { $employee->earn_trans_id_03 = [$result[2]->item_id, $result[2]->narration, $result[2]->formula]; $employee->earn_03 = $result[2]->formula; } else { $employee->earn_trans_id_03 = 0; $employee->earn_03 = 0; } if (count($result) > 3) { $employee->earn_trans_id_04 = [$result[3]->item_id, $result[3]->narration, $result[3]->formula]; $employee->earn_04 = $result[3]->formula; } else { $employee->earn_trans_id_04 = 0; $employee->earn_04 = 0; } if (count($result) > 4) { $employee->earn_trans_id_05 = [$result[4]->item_id, $result[4]->narration, $result[4]->formula]; $employee->earn_05 = $result[4]->formula; } else { $employee->earn_trans_id_05 = 0; $employee->earn_05 = 0; } if (count($result) > 5) { $employee->earn_trans_id_06 = [$result[5]->item_id, $result[5]->narration, $result[5]->formula]; $employee->earn_06 = $result[5]->formula; } else { $employee->earn_trans_id_06 = 0; $employee->earn_06 = 0; } if (count($result) > 6) { $employee->earn_trans_id_07 = [$result[6]->item_id, $result[6]->narration, $result[6]->formula]; $employee->earn_07 = $result[6]->formula; } else { $employee->earn_trans_id_07 = 0; $employee->earn_07 = 0; } if (count($result) > 7) { $employee->earn_trans_id_08 = [$result[7]->item_id, $result[7]->narration, $result[7]->formula]; $employee->earn_08 = $result[7]->formula; } else { $employee->earn_trans_id_08 = 0; $employee->earn_08 = 0; } if (count($result) > 8) { $employee->earn_trans_id_09 = [$result[8]->item_id, $result[8]->narration, $result[8]->formula]; $employee->earn_09 = $result[8]->formula; } else { $employee->earn_trans_id_09 = 0; $employee->earn_09 = 0; } if (count($result) > 9) { $employee->earn_trans_id_10 = [$result[9]->item_id, $result[9]->narration, $result[9]->formula]; $employee->earn_10 = $result[9]->formula; } else { $employee->earn_trans_id_10 = 0; $employee->earn_10 = 0; } } private function allDeductions($employee) { $query = "SELECT hr_transaction_item_setups.`narration` AS narration, hr_transaction_item_setups.`item_id` As item_id, hr_other_earning_deduction.`type` AS type, hr_other_earning_deduction.`formula` AS formula, hr_transaction_item_setups.`status` AS hr_transaction_item_setups_status FROM `hr_transaction_items` hr_transaction_items INNER JOIN `hr_other_earning_deduction` hr_other_earning_deduction ON hr_transaction_items.`item_id` = hr_other_earning_deduction.`item_id` INNER JOIN `hr_transaction_item_setups` hr_transaction_item_setups ON hr_transaction_items.`item_id` = hr_transaction_item_setups.`item_id` WHERE hr_other_earning_deduction.type = '3' AND hr_transaction_item_setups.status = '1'"; $result = DB::select($query); if (count($result) > 0) { $employee->deduct_trans_id_01 = [$result[0]->item_id, $result[0]->narration, $result[0]->formula]; $employee->deduct_01 = $result[0]->formula; } else { $employee->deduct_trans_id_01 = count($result); $employee->deduct_01 = 0; } if (count($result) > 1) { $employee->deduct_trans_id_02 = [$result[1]->item_id, $result[1]->narration, $result[1]->formula]; $employee->deduct_02 = $result[1]->formula; } else { $employee->deduct_trans_id_02 = 0; $employee->deduct_02 = 0; } if (count($result) > 2) { $employee->deduct_trans_id_03 = [$result[2]->item_id, $result[2]->narration, $result[2]->formula]; $employee->deduct_03 = $result[2]->formula; } else { $employee->deduct_trans_id_03 = 0; $employee->deduct_03 = 0; } if (count($result) > 3) { $employee->deduct_trans_id_04 = [$result[3]->item_id, $result[3]->narration, $result[3]->formula]; $employee->deduct_04 = $result[3]->formula; } else { $employee->deduct_trans_id_04 = 0; $employee->deduct_04 = 0; } if (count($result) > 4) { $employee->deduct_trans_id_05 = [$result[4]->item_id, $result[4]->narration, $result[4]->formula]; $employee->deduct_05 = $result[4]->formula; } else { $employee->deduct_trans_id_05 = 0; $employee->deduct_05 = 0; } if (count($result) > 5) { $employee->deduct_trans_id_06 = [$result[5]->item_id, $result[5]->narration, $result[5]->formula]; $employee->deduct_06 = $result[5]->formula; } else { $employee->deduct_trans_id_06 = 0; $employee->deduct_06 = 0; } if (count($result) > 6) { $employee->deduct_trans_id_07 = [$result[6]->item_id, $result[6]->narration, $result[6]->formula]; $employee->deduct_07 = $result[6]->formula; } else { $employee->deduct_trans_id_07 = 0; $employee->deduct_07 = 0; } if (count($result) > 7) { $employee->deduct_trans_id_08 = [$result[7]->item_id, $result[7]->narration, $result[7]->formula]; $employee->deduct_08 = $result[7]->formula; } else { $employee->deduct_trans_id_08 = 0; $employee->deduct_08 = 0; } if (count($result) > 8) { $employee->deduct_trans_id_09 = [$result[8]->item_id, $result[8]->narration, $result[8]->formula]; $employee->deduct_09 = $result[8]->formula; } else { $employee->deduct_trans_id_09 = 0; $employee->deduct_09 = 0; } if (count($result) > 9) { $employee->deduct_trans_id_10 = [$result[9]->item_id, $result[9]->narration, $result[9]->formula]; $employee->deduct_10 = $result[9]->formula; } else { $employee->deduct_trans_id_10 = 0; $employee->deduct_10 = 0; } } /** * responseBody * This is used to create response. * @param success This is the paramter require boolean * @param name This is the paramter require ui table name * @param message This is the paramter require message content * @param result This is the paramter require result as some of data to return client * @return Json This returns as response. */ private function responseBody($success, $name, $message, $result) { $body = [ "success" => $success, "message" => $message, "name" => $name, "result" => $result ]; return $body; } }