<?php
ini_set('display_errors',0);
date_default_timezone_set('Asia/Kuala_Lumpur');
include '/var/www/html/db/connectionADP.php';
$blQuery= "SELECT V.BL_NO,
            V.CONTAINER_NO,
            V.PO_NUMBER      
            FROM APPS.YTY_VW_PO_LATEX_TRACKER V
            WHERE V.ETA_DATE >=TRUNC(SYSDATE)-5";
 
$queryblDets = oci_parse($dbcon, $blQuery);
oci_execute($queryblDets);
$flag=0;
while($blRow=oci_fetch_array($queryblDets))
{
    //echo "<pre>";print_r($blRow);  die;
    $bl_no=trim($blRow['BL_NO']);  
    $containerNo=trim($blRow['CONTAINER_NO']);    
    $po_no=trim($blRow['PO_NUMBER']);    
    $sqlExists= "SELECT COUNT(1) DATA_EXIST FROM APPS.XXYTY_CONTAINER_BOOKING_DET WHERE BL_NO='".$bl_no."' AND CONTAINER_NO='".$containerNo."'";
    $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------ */
    {
        $queryData= "SELECT SID FROM APPS.XXYTY_CONTAINER_BOOKING_DET WHERE BL_NO='".$bl_no."' AND CONTAINER_NO='".$containerNo."'";
        $queryData = oci_parse($dbcon, $queryData);
        oci_execute($queryData);
        $returnData=oci_fetch_array($queryData);
        $queryData1= "DELETE FROM APPS.XXYTY_CONTAINER_BOOKING_DET WHERE SID='".$returnData['SID']."'";
        $queryDetData1 = oci_parse($dbcon, $queryData1);
        if(oci_execute($queryDetData1))
        {
            $queryData2= "DELETE FROM APPS.XXYTY_CONTAINER_ROUTE_DETAILS WHERE SID='".$returnData['SID']."'";
            $queryDetData2 = oci_parse($dbcon, $queryData2);
            oci_execute($queryDetData2);
        }
    }
    //$api_key='LURQ-OFES-W7ZN-D2AI-4X8V';
    $api_key='B8L6-31JW-Z2QE-7TYA-KV5C';
    $sea_line='AUTO';
    $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);
	
    if($maxRecord['MAX_ID'] >0)
    {
        //********************Insert 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=$resultCt->data->locations;
        $container=$resultCt->data->container;
        //echo "<pre>";print_r($resultCt);die;
        $sqlMultilevelDta = "INSERT INTO APPS.XXYTY_CONTAINER_BOOKING_DET(SID,BL_NO,CONTAINER_NO,CONTAINER_ISO_CODE,SO_NO) VALUES('".$maxRecord['MAX_ID']."','".$bl_no."','".$containerNo."','".$container->iso_code."','".$po_no."')";
	$executeQuery= oci_parse($dbcon,$sqlMultilevelDta);
        oci_execute($executeQuery);
        $locationArr=[];
        foreach($containerLocations as $locations)
        {
            $locationArr[$locations->id]= $locations->name."@_@".$locations->state."@_@".$locations->country."@_@".$locations->country_code."@_@".$locations->locode."@_@".$locations->lat."@_@".$locations->lng; 
        }
        $locationEvent=$resultCt->data->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,BL_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;
    }
}
if($flag==1)
{
    echo 'Record inserted successfully.';
}
else{
    echo 'Error in insertion.';
}
?>
