<?php
session_start();
ini_set('display_errors',0);
date_default_timezone_set('Asia/Kuala_Lumpur');
include '/var/www/html/db/connectionADP.php';

//echo "<pre>";print_r($_POST);die;
$bkQuery= " SELECT * FROM APPS.YTY_VW_SO_BOOKING_INFO D
	     WHERE d.BL_DATE is null
               and trunc(d.gate_out_date) >='01-JAN-26'";
$querybkDets = oci_parse($dbcon, $bkQuery);
oci_execute($querybkDets);
while($bkRow=oci_fetch_array($querybkDets))
{ 
    $bl_no=$bkRow['BOOKING_NO'];
    $sea_line=$bkRow['SEA_LINE_CODE'];
    $so_no=$bkRow['ORDER_NUMBER'];
    //echo "<pre>";print_r($bkRow);die;

    $sqlExists= "SELECT COUNT(1) DATA_EXIST FROM APPS.XXYTY_CONTAINER_BOOKING_DET WHERE BOOKING_NO='".$bl_no."' AND SO_NO='".$so_no."'";
    $queryExistsDets = oci_parse($dbcon, $sqlExists);
    oci_execute($queryExistsDets);
    $existDet=oci_fetch_array($queryExistsDets);
    //echo "<pre>";print_r($existDet);die;
    if($existDet['DATA_EXIST'] >0) //***************Update data here------ */
    {
        //$api_key='LURQ-OFES-W7ZN-D2AI-4X8V';
        $api_key='B8L6-31JW-Z2QE-7TYA-KV5C';
        $filePath="https://tracking.searates.com/reference?type=BK&number=$bl_no&sealine=$sea_line&api_key=$api_key";
        $ch = curl_init(); # initialize curl object
        curl_setopt($ch, CURLOPT_URL, $filePath); # set url
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, TRUE); # receive server response
        curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, FALSE); # do not verify SSL

        $data = curl_exec($ch); # execute curl
        $httpstatus = curl_getinfo($ch, CURLINFO_HTTP_CODE); # http response status code
        $result = json_decode($data);
        $conatinerDetails=$result->data->containers;
        //echo "<pre>";print_r($conatinerDetails);die;
        $flag=0;

        foreach($conatinerDetails as $container)
        {
            if($container->number!="")
            {
                $containerNo=$container->number;
                $queryData= "SELECT SID FROM APPS.XXYTY_CONTAINER_BOOKING_DET WHERE BOOKING_NO='".$bl_no."' AND CONTAINER_NO='".$containerNo."'";
                $queryData = oci_parse($dbcon, $queryData);
                oci_execute($queryData);
                $returnData=oci_fetch_array($queryData);
                //****************Delete first */
                $queryData1= "DELETE FROM APPS.XXYTY_CONTAINER_ROUTE_DETAILS WHERE SID='".$returnData['SID']."'";
                $queryData1 = oci_parse($dbcon, $queryData1);
                oci_execute($queryData1);
                //echo "<pre>";print_r($returnData);die;
                //********************UPDATE container information*******************************/
            // $filePath="https://tracking.searates.com/container?number= $containerNo&sealine=$sea_line&api_key=$api_key";
                //$ch = curl_init(); # initialize curl object
                //curl_setopt($ch, CURLOPT_URL, $filePath); # set url
                //curl_setopt($ch, CURLOPT_RETURNTRANSFER, TRUE); # receive server response
                //curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, FALSE); # do not verify SSL     
                //$dataCt= curl_exec($ch); # execute curl
                //$httpstatus = curl_getinfo($ch, CURLINFO_HTTP_CODE); # http response status code
                //$resultCt = json_decode($dataCt);
                $containerLocations=$result->data->locations;
                $locationArr=[];
                foreach($containerLocations as $locations)
                {
                    $locationArr[$locations->id]= $locations->name."@_@".$locations->state."@_@".$locations->country."@_@".$locations->country_code."@_@".$locations->locode."@_@".$locations->lat."@_@".$locations->lng; 
                }
                $locationEvent=$container->events;
                foreach($locationEvent as $event)
                {
                    list($lName,$state,$country,$cCode,$locode,$lati,$longi)=explode("@_@",$locationArr[$event->location]);
                    $route='N';
                    if($event->actual)
                        $route='Y';

                        $sqlMultilevel = "INSERT INTO APPS.XXYTY_CONTAINER_ROUTE_DETAILS(SID,BOOKING_NO,CONTAINER_NO,PORT_NAME,STATE,COUNTRY,COUNTRY_CODE,LOCODE,LAT,LNG,ACTUAL_ROUTE,DESCRIPTION_C,LOCATION_DATE,STATUS) VALUES('".$returnData['SID']."','".$bl_no."','".$container->number."','".$lName."','".$state."','".$country."','".$cCode."','".$locode."','".$lati."','".$longi."','".$route."','".$event->description."','".date('d-M-Y H:i:s',strtotime($event->date))."','".$event->status."')";
                       $execute1= oci_parse($dbcon,$sqlMultilevel);
                        oci_execute($execute1);
                        $flag=1;
                    }
                //echo "<pre>";print_r($locationArr);
                //echo "<pre>";print_r($locationEvent);die;
            }
        }

    }
    else
    { 
        //$api_key='LURQ-OFES-W7ZN-D2AI-4X8V';
        $api_key='B8L6-31JW-Z2QE-7TYA-KV5C';
        $filePath="https://tracking.searates.com/reference?type=BK&number=$bl_no&sealine=$sea_line&api_key=$api_key";
        $ch = curl_init(); # initialize curl object
        curl_setopt($ch, CURLOPT_URL, $filePath); # set url
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, TRUE); # receive server response
        curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, FALSE); # do not verify SSL

        $data = curl_exec($ch); # execute curl
        $httpstatus = curl_getinfo($ch, CURLINFO_HTTP_CODE); # http response status code
        $result = json_decode($data);
        $conatinerDetails=$result->data->containers ?? [];
        //echo "<pre>";print_r($conatinerDetails);
        $flag=0;
        if(count($conatinerDetails) > 0)
        {
        foreach($conatinerDetails as $container)
        {
            $sql = "SELECT NVL(MAX(SID)+1,1) MAX_ID FROM APPS.XXYTY_CONTAINER_BOOKING_DET";
            $queryDets = oci_parse($dbcon, $sql);
            oci_execute($queryDets);
            $maxRecord=oci_fetch_array($queryDets);
            //echo "<pre>";print_r($maxRecord);die;
            $sqlMultilevelDta = "INSERT INTO APPS.XXYTY_CONTAINER_BOOKING_DET(SID,BOOKING_NO,CONTAINER_NO,CONTAINER_ISO_CODE,BOOKING_TYPE,SO_NO,TRANSACTION_TYPE) 
            VALUES('".$maxRecord['MAX_ID']."','".$bl_no."','".$container->number."','".$container->iso_code."','BK','".$so_no."','E')";
            $executeQuery= oci_parse($dbcon,$sqlMultilevelDta);
            if(oci_execute($executeQuery))
            {
                //********************Insert container information*******************************/
                $containerNo=$container->number;
            // $filePath="https://tracking.searates.com/container?number= $containerNo&sealine=$sea_line&api_key=$api_key";
                //$ch = curl_init(); # initialize curl object
                //curl_setopt($ch, CURLOPT_URL, $filePath); # set url
                //curl_setopt($ch, CURLOPT_RETURNTRANSFER, TRUE); # receive server response
                //curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, FALSE); # do not verify SSL     
                //$dataCt= curl_exec($ch); # execute curl
                //$httpstatus = curl_getinfo($ch, CURLINFO_HTTP_CODE); # http response status code
                //$resultCt = json_decode($dataCt);
                $containerLocations=$result->data->locations;
                $locationArr=[];
                foreach($containerLocations as $locations)
                {
                    $locationArr[$locations->id]= $locations->name."@_@".$locations->state."@_@".$locations->country."@_@".$locations->country_code."@_@".$locations->locode."@_@".$locations->lat."@_@".$locations->lng; 
                }
                $locationEvent=$container->events;
                foreach($locationEvent as $event)
                {
                    list($lName,$state,$country,$cCode,$locode,$lati,$longi)=explode("@_@",$locationArr[$event->location]);
                    $route='N';
                    if($event->actual)
                        $route='Y';

                    $sqlMultilevel = "INSERT INTO APPS.XXYTY_CONTAINER_ROUTE_DETAILS(SID,BOOKING_NO,CONTAINER_NO,PORT_NAME,STATE,COUNTRY,COUNTRY_CODE,LOCODE,LAT,LNG,ACTUAL_ROUTE,DESCRIPTION_C,LOCATION_DATE,STATUS) VALUES('".$maxRecord['MAX_ID']."','".$bl_no."','".$container->number."','".$lName."','".$state."','".$country."','".$cCode."','".$locode."','".$lati."','".$longi."','".$route."','".$event->description."','".date('d-M-Y H:i:s',strtotime($event->date))."','".$event->status."')";
                    $execute1= oci_parse($dbcon,$sqlMultilevel);
                    oci_execute($execute1);
                    $flag=1;
                }
                //echo "<pre>";print_r($locationArr);
                //echo "<pre>";print_r($locationEvent);die;
            }
        } 
        }
        
    }
}
    	
?>
