USERID."' AND portal_name = 'MANPOWERDB'"; $sqlarr = oci_parse($dbcon , $sql); oci_execute($sqlarr); $rec_exists = "0"; while ($row = oci_fetch_array($sqlarr)) { $rec_exists = "1"; $access_by_dept = $row['ACCESS_BY_DEPT']; $access_by_location = $row['ACCESS_BY_LOCATION']; $access_by_level = $row['ACCESS_BY_LEVEL']; $access_by_emp = $row['ACCESS_BY_EMP']; } //If No Access, Lock The Query if ($rec_exists == "0") { $lock_condition = " and 1=0"; } else { $lock_condition = ""; } if (!empty($access_by_dept)) { $access_cond = " AND department_code||subdepartment_code IN ( SELECT dept_id||nvl(subdept_id, subdepartment_code) FROM production.attendance_user_access_dept@ytyhrdtoerpprod.ytygroup.com.my WHERE portal_name = 'MANPOWERDB' AND emp_id = '".$data->USERID."') "; } if (!empty($access_by_location)) { $access_cond = " AND orgid IN ( SELECT location_id FROM production.attendance_user_access_loc@ytyhrdtoerpprod.ytygroup.com.my WHERE portal_name = 'MANPOWERDB' AND emp_id = '".$data->USERID."') "; } if (!empty($access_by_level)) { $access_cond = " AND emp_level IN ( SELECT emp_level FROM production.attendance_user_access_level@ytyhrdtoerpprod.ytygroup.com.my WHERE portal_name = 'MANPOWERDB' AND emp_id = '".$data->USERID."') "; } if (!empty($access_by_emp)) { $access_cond = " AND emp_id IN ( SELECT member_id FROM production.attendance_user_access_emp@ytyhrdtoerpprod.ytygroup.com.my WHERE portal_name = 'MANPOWERDB' AND emp_id = '".$data->USERID."') "; } if (!empty($data->DEPARTMENT->ID)) { $whereDept = " AND a.department = '".$data->DEPARTMENT->ID."' "; } if (!empty($data->SUBDEPARTMENT->ID)) { $whereSubDept = " AND a.subdepartment_code = '".$data->SUBDEPARTMENT->ID."' "; } if (!empty($data->PLANT)) { switch($data->PLANT) { case "Lekir": $whereLocation = " AND a.orgid = '84' "; break; case "Acheh": $whereLocation = " AND a.orgid IN ('86','102','122') "; break; case "GTH": $whereLocation = " AND a.orgid = '98' "; break; case "KLO": $whereLocation = " AND a.orgid = '99' "; break; } } if (!empty($data->BLOCK)) { $whereBlock = " AND a.block = '".$data->BLOCK."' "; } if (!empty($data->JOB_LEVEL)) { $whereLevel = " AND a.emp_level = '".$data->JOB_LEVEL."' "; } if (!empty($data->EMP_ID->ID)) { $whereEmp = " AND a.emp_id = '".$data->EMP_ID->ID."' "; } if (!empty($data->HABITUAL)) { $habitual = $data->HABITUAL; } else { $habitual = 9999; } if (!empty($data->SHIFT)) { $whereShift = " AND case when to_date(b.in_time_str,'HH24:MI') between to_date('05:00','HH24:MI') and to_date('16:00','HH24:MI') then 'Day' when to_date(b.in_time_str,'HH24:MI') between to_date('05:00','HH24:MI') and to_date('16:00','HH24:MI') then 'Night' end = '".$data->SHIFT."' "; } $sql = "SELECT emp_id, new_emp_id, employee_name, department, sub_department, location, nationality, BLOCK, SUM(prsent_ph) prsent_ph, SUM(suppose_come) suppose_come, rtrim(to_char(SUM(prsent), 'FM90.9'), '.') prsent, designation, round(((SUM(prsent) / DECODE(SUM(suppose_come),0,1,SUM(suppose_come))) * 100), 1)||'%' attendance_p, rtrim(to_char(SUM(CASE WHEN suppose_come = 1 AND prsent = 0 AND yty_leave_code IS NULL THEN 1 WHEN suppose_come = 1 AND prsent = 0 AND yty_leave_code IN ('15EL', '09RH', '10HF','14UD') THEN 0.5 ELSE 0 END), 'FM90.9'), '.') ABS, rtrim(to_char(SUM(CASE WHEN suppose_come = 1 AND yty_leave_code IN ('01AL', '11EL', '08RF') THEN 1 WHEN suppose_come = 1 AND yty_leave_code IN ('15EL', '09RH', '10HF') THEN 0.5 ELSE 0 END), 'FM90.9'), '.') AL, rtrim(to_char(SUM(CASE WHEN suppose_come = 1 AND yty_leave_code IN ('02SL','11HO') THEN 1 ELSE 0 END), 'FM90.9'), '.') MC, rtrim(to_char(SUM(CASE WHEN suppose_come = 1 AND yty_leave_code IN ('07UP') THEN 1 WHEN suppose_come = 1 AND yty_leave_code IN ('14UD') THEN 0.5 ELSE 0 END), 'FM90.9'), '.') UL, rtrim(to_char(SUM(CASE WHEN suppose_come = 1 AND yty_leave_code IN ('06ML') THEN 1 ELSE 0 END), 'FM90.9'), '.') ML, rtrim(to_char(SUM(CASE WHEN suppose_come = 1 AND yty_leave_code IN ('03ML','04PL','05CL','13OD') THEN 1 ELSE 0 END), 'FM90.9'), '.') Others, SUM(nvl(no_ot,0)) NO_OT, SUM(nvl(late_flag,0)) LATE_IN, CASE WHEN SUM(nvl(late_flag,0)) > ".$habitual." THEN 'Yes' ELSE 'No' END HABITUAL FROM ( SELECT a.emp_id, a.new_emp_id, a.employee_name, a.block, a.nationality, case when a.orgid = '84' then 'Lekir' when a.orgid in ('86','102','122') then 'Acheh' when a.orgid = '98' then 'GTH' when a.orgid = '99' then 'KLO' end location, a.designation, a.department, a.sub_department, a.dt, c.holiday_name, CASE WHEN c.holiday_name IS NULL THEN CASE d.count_wk WHEN 0 THEN CASE WHEN TRIM(TO_CHAR(a.dt, 'DAY')) IN ('SUNDAY', 'SATURDAY') THEN 0 ELSE 1 END ELSE CASE WHEN e.weekoff_type IS NOT NULL THEN 0 ELSE 1 END END ELSE 0 END suppose_come, CASE WHEN f.yty_leave_code IS NULL THEN CASE WHEN b.in_time IS NOT NULL OR b.out_time IS NOT NULL THEN CASE WHEN c.holiday_name IS NULL THEN CASE d.count_wk WHEN 0 THEN CASE WHEN TRIM(TO_CHAR(a.dt, 'DAY')) IN ('SUNDAY', 'SATURDAY') THEN 0 ELSE 1 END ELSE CASE WHEN e.weekoff_type IS NOT NULL THEN 0 ELSE 1 END END ELSE 0 END ELSE 0 END WHEN f.yty_leave_code IN ('15EL', '09RH', '10HF','14UD') THEN CASE WHEN b.in_time IS NOT NULL OR b.out_time IS NOT NULL THEN CASE WHEN c.holiday_name IS NULL THEN CASE d.count_wk WHEN 0 THEN CASE WHEN TRIM(TO_CHAR(a.dt, 'DAY')) IN ('SUNDAY', 'SATURDAY') THEN 0 ELSE 0.5 END ELSE CASE WHEN e.weekoff_type IS NOT NULL THEN 0 ELSE 0.5 END END ELSE 0 END ELSE 0 END ELSE 0 END prsent, CASE WHEN c.holiday_name IS NOT NULL AND b.in_time IS NOT NULL AND b.out_time IS NOT NULL THEN 1 ELSE 0 END prsent_ph, f.yty_leave_code, case when b.in_time_str is not null and b.out_time_str is not null then CASE WHEN NVL( case when b.in_time_str is not null and b.out_time_str is not null then case when NVL(a.emp_pay_division,'-') = '1801' then --OT Day Shift case when (to_date(b.in_time_str,'HH24:MI') between to_date('06:00','HH24:MI') and to_date('15:00','HH24:MI')) and (to_date(b.out_time_str,'HH24:MI') between to_date('16:00','HH24:MI') and to_date('21:00','HH24:MI')) then case when to_date(b.out_time_str,'HH24:MI') between to_date('17:00','HH24:MI') and to_date('17:00','HH24:MI') then '0.50' when to_date(b.out_time_str,'HH24:MI') between to_date('17:01','HH24:MI') and to_date('17:15','HH24:MI') then '0.75' when to_date(b.out_time_str,'HH24:MI') between to_date('17:16','HH24:MI') and to_date('17:30','HH24:MI') then '1' when to_date(b.out_time_str,'HH24:MI') between to_date('17:31','HH24:MI') and to_date('17:45','HH24:MI') then '1.25' when to_date(b.out_time_str,'HH24:MI') between to_date('17:46','HH24:MI') and to_date('18:00','HH24:MI') then '1.5' when to_date(b.out_time_str,'HH24:MI') between to_date('18:01','HH24:MI') and to_date('18:15','HH24:MI') then '1.75' when to_date(b.out_time_str,'HH24:MI') between to_date('18:16','HH24:MI') and to_date('18:30','HH24:MI') then '2' when to_date(b.out_time_str,'HH24:MI') between to_date('18:31','HH24:MI') and to_date('18:45','HH24:MI') then '2.25' when to_date(b.out_time_str,'HH24:MI') >= to_date('18:46','HH24:MI') then '2.5' else '0' end --OT Night Shift when (to_date(b.in_time_str,'HH24:MI') between to_date('18:00','HH24:MI') and to_date('23:59','HH24:MI')) and (to_date(b.out_time_str,'HH24:MI') between to_date('04:00','HH24:MI') and to_date('09:00','HH24:MI')) then case when to_date(b.out_time_str,'HH24:MI') between to_date('05:00','HH24:MI') and to_date('05:00','HH24:MI') then '0.50' when to_date(b.out_time_str,'HH24:MI') between to_date('05:01','HH24:MI') and to_date('05:15','HH24:MI') then '0.75' when to_date(b.out_time_str,'HH24:MI') between to_date('05:16','HH24:MI') and to_date('05:30','HH24:MI') then '1' when to_date(b.out_time_str,'HH24:MI') between to_date('05:31','HH24:MI') and to_date('05:45','HH24:MI') then '1.25' when to_date(b.out_time_str,'HH24:MI') between to_date('05:46','HH24:MI') and to_date('06:00','HH24:MI') then '1.5' when to_date(b.out_time_str,'HH24:MI') between to_date('06:01','HH24:MI') and to_date('06:15','HH24:MI') then '1.75' when to_date(b.out_time_str,'HH24:MI') between to_date('06:16','HH24:MI') and to_date('06:30','HH24:MI') then '2' when to_date(b.out_time_str,'HH24:MI') between to_date('06:31','HH24:MI') and to_date('06:45','HH24:MI') then '2.25' when to_date(b.out_time_str,'HH24:MI') >= to_date('06:46','HH24:MI') then '2.5' else '0' end else '0' end end end , 0) < 2.5 THEN 1 ELSE 0 END else 0 end no_ot, case when b.in_time_str is not null and b.out_time_str is not null then case when NVL(a.emp_pay_division,'-') = '1801' then case when to_date(b.in_time_str,'HH24:MI') between to_date('05:00','HH24:MI') and to_date('16:00','HH24:MI') then case when to_date('07:30','HH24:MI') - to_date(b.in_time_str,'HH24:MI') < 0 then 1 else 0 end else case when to_date('19:30','HH24:MI') - to_date(b.in_time_str,'HH24:MI') < 0 then 1 else 0 end end else case when to_date(b.in_time_str,'HH24:MI') between to_date('05:00','HH24:MI') and to_date('16:00','HH24:MI') then case when to_date('08:30','HH24:MI') - to_date(b.in_time_str,'HH24:MI') < 0 then 1 else 0 end end end end late_flag, case when to_date(b.in_time_str,'HH24:MI') between to_date('05:00','HH24:MI') and to_date('16:00','HH24:MI') then 'Day' when to_date(b.in_time_str,'HH24:MI') between to_date('05:00','HH24:MI') and to_date('16:00','HH24:MI') then 'Night' end shift_group FROM (SELECT emp_id, CASE WHEN length(new_emp_id) > 6 THEN substr(new_emp_id, 4) ELSE new_emp_id END new_emp_id, employee_name, location, block, emp_level, nationality, designation, department, sub_department, subdepartment_code, emp_status, orgid, dt, emp_pay_division FROM production.employee_master@YTYHRDTOERPPROD.YTYGROUP.COM.MY, (SELECT TO_DATE(:from_date, 'YYYY-MM-DD') + LEVEL - 1 dt FROM dual CONNECT BY LEVEL <= (TO_DATE(:to_date, 'YYYY-MM-DD') - TO_DATE(:from_date, 'YYYY-MM-DD') + 1)) WHERE NVL(virtual_flag,'N') = 'N' ".$access_cond.") a, payroll.prepared_attendance b, (SELECT holiday_date, holiday_name, location_code FROM holiday_master WHERE holiday_date BETWEEN TO_DATE(:from_date, 'YYYY-MM-DD') AND TO_DATE(:to_date, 'YYYY-MM-DD') UNION SELECT holiday_effective_date, holiday_name, location_code FROM holiday_master WHERE holiday_effective_date BETWEEN TO_DATE(:from_date, 'YYYY-MM-DD') AND TO_DATE(:to_date, 'YYYY-MM-DD')) c, (SELECT w_employee_id, COUNT(w_employee_id) count_wk FROM production.xxyty_week_off_master@ytyhrdtoerpprod.ytygroup.com.my WHERE w_week_date BETWEEN TO_DATE(:from_date, 'YYYY-MM-DD') AND TO_DATE(:to_date, 'YYYY-MM-DD') GROUP BY w_employee_id) d, (SELECT w_week_date, w_employee_id, weekoff_type FROM production.xxyty_week_off_master@ytyhrdtoerpprod.ytygroup.com.my WHERE w_week_date BETWEEN TO_DATE(:from_date, 'YYYY-MM-DD') AND TO_DATE(:to_date, 'YYYY-MM-DD')) e, (SELECT DISTINCT emp_no, doc_no, yty_leave_code, column_value AS dt FROM leave_transaction_data_yty_new, TABLE(dates_between(leave_from, leave_to)) WHERE column_value BETWEEN TO_DATE(:from_date, 'YYYY-MM-DD') AND TO_DATE(:to_date, 'YYYY-MM-DD')) f WHERE a.new_emp_id = b.emp_no(+) AND a.dt = b.access_date(+) AND nvl(a.emp_status, 'N') = 'N' AND a.department IS NOT NULL ".$lock_condition.$whereDept.$whereSubDept.$whereLocation.$whereBlock.$whereLevel.$whereEmp.$whereShift." AND a.dt BETWEEN TO_DATE(:from_date, 'YYYY-MM-DD') AND TO_DATE(:to_date, 'YYYY-MM-DD') AND a.dt = c.holiday_date(+) AND case a.orgid when 98 then 98 when 99 then 99 else 97 end = case c.location_code(+) when 0 then case a.orgid when 98 then 98 when 99 then 99 else 97 end else c.location_code(+) end AND a.new_emp_id = d.w_employee_id(+) AND a.new_emp_id = e.w_employee_id(+) AND a.dt = e.w_week_date(+) AND a.new_emp_id = f.emp_no(+) AND a.dt = f.dt(+)) GROUP BY emp_id, new_emp_id, employee_name, department, sub_department, location, nationality, block, designation"; $sqlarr = oci_parse($dbcon , $sql); oci_bind_by_name($sqlarr, ':from_date', $data->FROM_DATE); oci_bind_by_name($sqlarr, ':to_date', $data->TO_DATE); $r = oci_execute($sqlarr); if (!$r) { $e = oci_error($sqlarr); print json_encode($e['message']); } oci_fetch_all($sqlarr, $rows, null, null, OCI_FETCHSTATEMENT_BY_ROW); print json_encode($rows); ?>