<?php
    include "../db/headerApi.php";
    include "../db/connection_main.php";

    $json = file_get_contents('php://input');
    $data = json_decode($json);

    if (!empty($data->from_date) && !empty($data->to_date)) {
       $whereDate = " AND a.visit_from BETWEEN TO_DATE('".$data->from_date."','YYYY-MM-DD') AND TO_DATE('".$data->to_date."','YYYY-MM-DD') ";
    } else {
       $whereDate = " AND TRUNC(SYSDATE) between a.visit_from and a.visit_to ";
    }

    if (!empty($data->visit_type)) {
       $whereType = " AND a.visit_type = '".$data->visit_type."' ";
    }

    if (!empty($data->show_all)) {
       $whereStatus = "";
    } else {
       $whereStatus = " AND a.status != 1 ";
    }

    //Get User Location
    $sql = " SELECT CASE to_char(default_org) WHEN '84' THEN 'YTYL' WHEN '102' THEN 'YTYA' WHEN '122' THEN 'YTYA' WHEN '86' THEN 'GPA' WHEN '98' THEN 'GTH' END loc,
                    NVL(admin_vms,'N') admin_vms
               FROM ytycustom.user_registry
              WHERE logon_name = :userid";
    $sqlarr = oci_parse($dbcon, $sql);
    oci_bind_by_name($sqlarr, ':userid', $data->user_id);
    $r = oci_execute($sqlarr);
    while ($row = oci_fetch_array($sqlarr)) {
       $location = $row['LOC'];
       $admin_vms = $row['ADMIN_VMS'];
    }

    if ($admin_vms == 'Y') {
      $wherePIC = " AND a.location = '".$location."' ";
    } else if ($admin_vms == 'A') {
      $wherePIC = "";
    } else {
      $wherePIC = " AND a.pic = '".$data->user_id."' ";
    }

    $sql = " SELECT 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,
                    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, a.reason, a.remarks,
                    '<ul class=''visname''>'||replace(replace(RTRIM(XMLAGG(XMLELEMENT(e, '|'||c.fullname||'~', '').EXTRACT('//text()')), ','),'|','<li>'),'~','</li>')||'</ul>' visitor_name
               FROM production.yty_visit_info a, production.employee_master b,
                    production.yty_visit_members c
              WHERE a.pic = b.emp_id
                AND a.visit_id = c.visit_id
                AND a.cancel_date IS NULL
                ".$whereStatus.$whereDate.$whereType.$wherePIC."
           GROUP BY a.visit_id, a.pic, b.employee_name, a.visit_from, a.visit_to,
                    a.visit_type, a.reason, a.remarks
           ORDER BY a.visit_from DESC, a.visit_id DESC";
               
    $sqlarr = oci_parse($dbcon , $sql);
    $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);
?>