<?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_received 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 a.brand = '".$data->BRAND->ID."' ";
    }

    $refno = "";
    if (!empty($data->REF_NO)) {
        $refno = " AND a.ref_no LIKE '%".$data->REF_NO."%' ";
    }

    $irrd = "";
    if (!empty($data->IRRD)) {
        $irrd = " AND a.irrd LIKE '%".$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, a.ref_no, b.customer_name, c.brand_name, 
                    to_char(a.date_received, 'DD/MM/YYYY') date_received,
                    a.total_ctn, a.irrd, a.id||'|'||a.customer qr
               FROM ytycustom.xx_yty_incoming_outgoing_inspection_tbl a,
                    apps.xxyty_cust_gth_v b,
                    ytycustom.gth_customer_brand_master c
              WHERE a.customer = b.customer_id
                AND a.brand = c.brand_id
               ".$fromto."
               ".$customer."
               ".$brand."
               ".$refno."
               ".$irrd."
               ".$qrcode."
           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);
?>