<?php
    include "../db/headerApi.php";
    include "../db/connection_main.php";

    $json = file_get_contents('php://input');
    $data = json_decode($json);

    $fromto = "";
    if (!empty($data->FROM_DATE) && !empty($data->TO_DATE)) {
        $fromto = " AND a.date_out BETWEEN to_date('".$data->FROM_DATE."','YYYY-MM-DD')
                                       AND to_date('".$data->TO_DATE."','YYYY-MM-DD') ";
    }

    $customer = "";
    if (!empty($data->CUSTOMER->ID)) {
        $customer = " AND a.customer = '".$data->CUSTOMER->ID."' ";
    }

    $brand = "";
    if (!empty($data->BRAND->ID)) {
        $brand = " AND brand = '".$data->BRAND->ID."' ";
    }

    $refno = "";
    if (!empty($data->REF_NO)) {
        $refno = " AND ref_no like '%".$data->REF_NO."%' ";
    }

    $irrd = "";
    if (!empty($data->IRRD)) {
        $irrd = " AND irrd = '".$data->IRRD."' ";
    }

    $qrcode = "";
    if (!empty($data->QR_CODE)) {
        $qrcode = " AND a.id||'!'||a.customer = '".$data->QR_CODE."' ";
    }

    $offset = " OFFSET ".($data->PAGE * $data->NOOFROWS)." ROWS FETCH NEXT ".$data->NOOFROWS." ROWS ONLY";

    //Query All Records
    $sql = " SELECT a.id||'!'||a.customer qr, b.customer_name, to_char(a.date_out, 'DD/MM/YYYY') date_out, a.total_ctn, 
                    decode(a.status, 'R', 'Released', NULL) status, c.employee_name authorized_by, 
                    to_char(a.authorized_date, 'DD/MM/YYYY') authorized_date,
                    a.id||'!'||a.customer qr
               FROM ytycustom.xx_yty_incoming_outgoing_inspection_ship_hd a, 
                    apps.xxyty_cust_gth_v b, 
                    production.employee_master c
              WHERE a.customer = b.customer_id
                AND a.authorized_by = c.emp_id(+)
               ".$fromto."
               ".$customer."
               ".$qrcode."
                AND a.id in (SELECT DISTINCT ship_id
                               FROM ytycustom.xx_yty_incoming_outgoing_inspection_detail_stl_part
                              WHERE 1=1
                                ".$brand."
                                ".$refno."
                                ".$irrd.")
           ORDER BY a.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);
    $totalCount = count($rows);
    
    //Pagination
    $sql = $sql.$offset;
    $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);

    $result[] = [
        "pagelength" => ceil($totalCount / $data->NOOFROWS),
        "data" => $rows
    ];
    
    print json_encode($result);
?>