<?php
    include "../db/headerApi.php";
    include "../db/connection_main.php";

    $json = file_get_contents('php://input');
    $data = json_decode($json);

    $sql = " SELECT :userid user_id, a.visit_id, a.pic, b.employee_name pic_name,
                    to_char(a.visit_from, 'DD/MM/YYYY') visit_from,
                    to_char(a.visit_to, 'DD/MM/YYYY') visit_to,
                    a.visit_type,
                    CASE a.visit_type
                        WHEN 1 THEN
                        'General'
                        WHEN 2 THEN
                        'Contractor'
                        WHEN 3 THEN
                        'Customer'
                        WHEN 4 THEN
                        'Vendor'
                        WHEN 5 THEN
                        'Enforcement Agencies'
                        END visit_type_desc, 
                    a.reason, a.remarks, a.location, a.status
               FROM production.yty_visit_info a, production.employee_master b
              WHERE a.pic = b.emp_id
                AND a.visit_id = :visit_id";
               
    $sqlarr = oci_parse($dbcon , $sql);
    oci_bind_by_name($sqlarr, ':visit_id', $data->visit_id);
    oci_bind_by_name($sqlarr, ':userid', $data->user_id);
    $r = oci_execute($sqlarr);

    if (!$r) {
         $e = oci_error($sqlarr);
         print json_encode($e['message']);
    }
    oci_fetch_all($sqlarr, $rows_main, null, null, OCI_FETCHSTATEMENT_BY_ROW);

    //Check Admin Access
    $sql = " SELECT NVL(admin_vms, 'N') admin_vms
               FROM ytycustom.user_registry
              WHERE logon_name = :logon_name";
    $sqlarr = oci_parse($dbcon , $sql);
    oci_bind_by_name($sqlarr, ':logon_name', $data->user_id);
    $r = oci_execute($sqlarr);
    while ($row = oci_fetch_array($sqlarr)) {
        $admin = $row['ADMIN_VMS'];
    }

    $sql = " SELECT a.mem_id, a.visit_id, a.fullname, 
                    '(' || substr(a.phone, 1, 3) || ')' || substr(a.phone, 4, 2) || '-' || substr(a.phone, 6, 4) ||'-'|| substr(a.phone, 10) phone,
                    a.email, a.ic_passport,
                    a.remarks, a.badge_num, a.plate_no, case a.wifi when 1 then 'Yes' else 'No' end wifi, 
                    a.wifi_pass, c.territory_short_name country, a.file_path,
                    case when '".$admin."' = 'Y' then 
                      case when trunc(sysdate) between z.visit_from and z.visit_to then 
                        case when b.time_in IS NULL then
                          null
                        else
                          'Y'
                        end
                      else
                        'Y' 
                      end
                    else
                      'Y'
                    end cin,
                    case when z.pic = :userid then 
                      case when b.ack_time is not null then 
                        'Y'
                      else 
                        case when b.time_in is not null then
                          null
                        else
                          'Y'
                        end
                      end
                    else 
                        'Y'
                    end ack,
                    case when '".$admin."' = 'Y' then 
                      case when b.time_out IS NULL then
                        case when b.time_in is not null then
                          null
                        else
                          'Y'
                        end
                      else
                        'Y'
                      end
                    else
                      'Y'
                    end cout,
                    case when b.time_out is not null then
                      'Checked Out'
                    when b.ack_time is not null then
                      'Acknowledged'
                    when b.time_in is not null then
                      'Checked In'
                    end status
               FROM production.yty_visit_info z,
                    production.yty_visit_members a,
                    (select * 
                       from production.yty_visit_logs 
                      where trunc(time_in) = trunc(sysdate)) b,
                    apps.fnd_territories_vl c
              WHERE z.visit_id = a.visit_id
                AND a.visit_id = b.visit_id(+)
                AND a.mem_id = b.mem_id(+)
                AND a.country = c.territory_code(+)
                AND a.visit_id = :visit_id
           ORDER BY a.mem_id";

    $sqlarr = oci_parse($dbcon , $sql);
    oci_bind_by_name($sqlarr, ':visit_id', $data->visit_id);
    oci_bind_by_name($sqlarr, ':userid', $data->user_id);
    $r = oci_execute($sqlarr);

    if (!$r) {
         $e = oci_error($sqlarr);
         print json_encode($e['message']);
    }
    oci_fetch_all($sqlarr, $rows_vis, null, null, OCI_FETCHSTATEMENT_BY_ROW);

    foreach ($rows_vis as $vis) {
      if (!empty($vis["STATUS"])) {
        $visit_happen = "Y";
      }
    }

    $rows_main[0] += [
        "VISITORS" => $rows_vis,
        "VISIT_HAPPEN" => $visit_happen 
    ];
    
    print json_encode($rows_main);
?>