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']; } //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 = " WHERE department_code IN ( SELECT dept_id FROM production.attendance_user_access_dept@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->PLANT)) { $whereLocation = " AND a.location = '".$data->PLANT."' "; } if (!empty($data->BLOCK)) { $whereBlock = " AND a.block = '".$data->BLOCK."' "; } if (!empty($data->JOB_LEVEL)) { $whereLevel = " AND a.emp_level = '".$data->JOB_LEVEL."' "; } $sql = "SELECT trim(to_char(dt,'Month')) label, SUM(CASE WHEN suppose_come = 1 AND prsent = 0 AND yty_leave_code IS NULL THEN 1 ELSE 0 END) ABS, round(((SUM(CASE WHEN suppose_come = 1 AND prsent = 0 AND yty_leave_code IS NULL THEN 1 ELSE 0 END) / SUM(suppose_come)) * 100),2) abs_rate FROM ( SELECT a.emp_id, a.new_emp_id, a.employee_name, a.location, a.block, a.designation, a.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 b.in_time IS NOT NULL AND b.out_time IS NOT NULL THEN 1 ELSE 0 END prsent, f.yty_leave_code 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, emp_status, orgid, dt 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))".$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 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.$whereLocation.$whereBlock.$whereLevel." 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 to_char(dt,'Month'), EXTRACT(month FROM dt) ORDER BY EXTRACT(month FROM dt)"; $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); ?>