<?php
    include "../db/headerApi.php";
    include "../db/connection_main.php";

    $json = file_get_contents('php://input');
    $data = json_decode($json);

    $sql = "SELECT emp_id, employee_name, department, designation, 'YTY Group' company,
                   0 entitlement, 0 mc_taken, 0 mc_balance, 0 unpain_taken
              FROM production.employee_master
             WHERE emp_id = :emp_id
               AND nvl(emp_status, 'N') = 'N'
               --AND nvl(virtual_flag, 'N') = 'N'
               ";

    $sqlarr = oci_parse($dbcon , $sql);
    oci_bind_by_name($sqlarr, ':emp_id', $data->EMP_ID->ID);
    $r = oci_execute($sqlarr);
    if (!$r) {
         $e = oci_error($sqlarr);
         $resMsg = json_encode($e['message']);
    }
    oci_fetch_all($sqlarr, $rows_emp, null, null, OCI_FETCHSTATEMENT_BY_ROW);

    if (!empty($rows_emp)) {
          $sql = "SELECT a.yr, a.emp_no, a.leave_type, nvl(a.leave_ent,0) leave_ent, nvl(b.days_taken,0) days_taken, 
                         (nvl(a.leave_ent,0)-nvl(b.days_taken,0)) leave_balance,
                         nvl(c.days_taken,0) unpaid_leave
                    FROM production.employee_master em,
                         ytycustom.expat_leave_balance_master a,
                         (SELECT to_char(ldp_from_date, 'YYYY') yr, emp_no,
                                 SUM((ldp_to_date - ldp_from_date) + 1) days_taken
                            FROM ytycustom.emp_leave_entry_hd b
                           WHERE ldp_code = '02SL'
                             AND nvl(cancel_flag, 'N') = 'N'
                             AND nvl(status, '-') = 'APPROVED'
                             AND emp_no = :emp_id
                             AND to_char(ldp_from_date, 'YYYY') = to_char(SYSDATE, 'YYYY')
                           GROUP BY to_char(ldp_from_date, 'YYYY'), emp_no) b,
                         (SELECT to_char(ldp_from_date, 'YYYY') yr, emp_no,
                                 SUM(case when ldp_code = '14UD' then 0.5 else (ldp_to_date - ldp_from_date) + 1 end) days_taken
                            FROM ytycustom.emp_leave_entry_hd b
                           WHERE ldp_code IN ('07UP','14UD')
                             AND nvl(cancel_flag, 'N') = 'N'
                             AND nvl(status, '-') = 'APPROVED'
                             AND emp_no = :emp_id
                             AND to_char(ldp_from_date, 'YYYY') = to_char(SYSDATE, 'YYYY')
                           GROUP BY to_char(ldp_from_date, 'YYYY'), emp_no) c  
                   WHERE em.emp_id = a.emp_no(+) 
                     AND a.yr = b.yr(+)
                     AND a.emp_no = b.emp_no(+)
                     AND a.yr = c.yr(+)
                     AND a.emp_no = c.emp_no(+)
                     AND a.yr(+) = to_char(SYSDATE, 'YYYY')
                     AND em.emp_id = :emp_id
                     AND a.leave_type(+) = '02SL'
                     AND nvl(em.emp_status, 'N') = 'N'
                     --AND nvl(em.virtual_flag, 'N') = 'N'
                     ";

          $sqlarr = oci_parse($dbcon , $sql);
          oci_bind_by_name($sqlarr, ':emp_id', $data->EMP_ID->ID);
          $r = oci_execute($sqlarr);
          if (!$r) {
               $e = oci_error($sqlarr);
               $resMsg = json_encode($e['message']);
          }
          oci_fetch_all($sqlarr, $rows_leave, null, null, OCI_FETCHSTATEMENT_BY_ROW);

          if (!empty($rows_leave)) {
                $rows_emp[0]["ENTITLEMENT"] = $rows_leave[0]["LEAVE_ENT"];
                $rows_emp[0]["MC_TAKEN"] = $rows_leave[0]["DAYS_TAKEN"];
                $rows_emp[0]["MC_BALANCE"] = $rows_leave[0]["LEAVE_BALANCE"];
                $rows_emp[0]["UNPAIN_TAKEN"] = $rows_leave[0]["UNPAID_LEAVE"];
          }

          $resMsg = "Success";

    } else {
          $resMsg = "No data found";
    }
    
    $result[] = [
        "message" => $resMsg,
        "data" => $rows_emp
    ];

    print json_encode($result);
?>