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)) { if ($data->PLANT == "Lekir") { $whereLocation = " AND a.orgid = '84' "; } else { $whereLocation = " AND a.orgid IN ('86','102','122') "; } } 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."' "; } $sql = "SELECT * FROM ( SELECT department, location, round(((SUM(prsent)/SUM(suppose_come))*100),2) attendance_p 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 f.yty_leave_code IN ('15EL', '09RH', '10HF','14UD') THEN 0.5 ELSE CASE WHEN b.in_time IS NOT NULL AND b.out_time IS NOT NULL THEN CASE WHEN c.holiday_name IS NULL THEN 1 ELSE 0 END ELSE 0 END END prsent, 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 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 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.$whereLocation.$whereBlock.$whereLevel.$whereEmp." 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 department, location) PIVOT (SUM(attendance_p) FOR location IN ('YTY L' as YTYL,'YTY A' as YTYA,'GPL' as GPL,'GP' as GP)) ORDER BY 1,2"; $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); ?>