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->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 ROUND(SUM(prsent) / noof_days) attend, ROUND(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) / noof_days) abs, ROUND(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) / noof_days) al, ROUND(SUM(CASE WHEN suppose_come = 1 AND yty_leave_code IN ('02SL','11HO') THEN 1 ELSE 0 END) / noof_days) mc, ROUND(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) / noof_days) ul, ROUND(SUM(CASE WHEN suppose_come = 1 AND yty_leave_code IN ('06ML') THEN 1 ELSE 0 END) / noof_days) ml, ROUND(SUM(CASE WHEN suppose_come = 1 AND yty_leave_code IN ('03ML','04PL','05CL','13OD') THEN 1 ELSE 0 END) / noof_days) oth FROM ( SELECT a.emp_id, a.new_emp_id, a.employee_name, a.location, a.block, a.designation, a.department, a.subdepartment_code, a.dt, c.holiday_name, (TO_DATE(:to_date, 'YYYY-MM-DD') - TO_DATE(:from_date, 'YYYY-MM-DD') + 1) noof_days, 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, f.yty_leave_code, 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.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.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) = 0 THEN 1 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, designation, 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(+)) ORDER BY 1"; $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']); die(); } oci_fetch_all($sqlarr, $rows, null, null, OCI_FETCHSTATEMENT_BY_ROW); print json_encode($rows, JSON_NUMERIC_CHECK); ?>