<?php
/**
 * @return array
 * @param array $hawb
 * @param string $loc
 * @desc A function that checks the existence of a House AWB in WM9. Returns a record set if a match is found, and returns False if no match found.
 * @author GoLivePacific
 */
function GetMatchedRecords($hawb, $loc) {
	//include file for database connection: oracle_connect.php for oracle and mysql_connect.php for mysql
	require_once('includes/config.php');
	require_once('includes/oracle_connect.php');	
	require_once('includes/mysql_connect.php');
	
	$statusarr = array();				//array containing the resultset being returned to client
	$index = 0;							//index identifying elements in statusarr

	//loop through all submitted HAWBs
	for ($x = 0; $x < sizeof($hawb); $x++){
		if ($hawb[$x] != '' && $hawb[$x] != NULL && $hawb[$x] != "\n" && !empty($hawb[$x])){
			//make sure that the hawb is trimmed and rid of unnecessary characters
			$hawb[$x] = str_replace("\n","",$hawb[$x]);		
			$hawb[$x] = str_replace("\r","",$hawb[$x]);					
			$hawb[$x] = str_replace(" ","",$hawb[$x]);					
	
			$sql = 'BEGIN '.$warehouse.'.chiweb_getmatchedrecord(:i_HAWB, :status_details); END;';
			$stmt = oci_parse($conn, $sql);
			
			// Bind the input i_HAWB argument to the $hawb PHP variable
			oci_bind_by_name($stmt,":i_HAWB",$hawb[$x],30);
			
			// Create a new cursor resource
			$status_details = oci_new_cursor($conn);
		
			// Bind the cursor resource to the Oracle argument
			oci_bind_by_name($stmt,":status_details",$status_details,-1,SQLT_RSET);
			
			// Execute the statement
			oci_execute($stmt, OCI_DEFAULT);
			
			// Execute the cursor
			oci_execute($status_details);
			
			// Use fetchassoc to get rows; $counter as counter of matched rows per hawb
			$counter = 0;
			//loop through fetched rows and assign values as necessary
			while($row = oci_fetch_assoc($status_details)){			
				$statusarr[$index]['hawb'] = $row['HAWB'];			
				$statusarr[$index]['mawb'] = $row['MAWB'];		
				$statusarr[$index]['arrvdate'] = $row['ARRVDATE'];				
				$statusarr[$index]['shiptype'] = $row['SHIPTYPE'];
				$statusarr[$index]['statusprocess'] = $row['STATUSPROCESS'];
				//if the status is MOVE, default wm9status to Move, otherwise just use description			
				if ($row['STATUSPROCESS'] == $const['move']){
					$wm9status = $const['mysql_move'];					
				}
				else{
					$wm9status = $row['DESCRIPTION'];
				}
				//retrieve the description from the mysql database
				$query = "SELECT SHIPMENTSTATUS.DESCRIPTION FROM SHIPMENTSTATUS WHERE WM9STATUS = '$wm9status'";
				$rs2 = mysql_query($query);
				$row2 = mysql_fetch_assoc($rs2);
				//if the description does not exist in the database, put the default status
				if (mysql_num_rows($rs2) == 0){
/*					if ($row['STATUSPROCESS'] == $const['receipt']){
						$statusarr[$index]['description'] = $const['in_receiving'];					
					}
					elseif ($row['STATUSPROCESS'] == $const['order']){
						$statusarr[$index]['description'] = $const['not_started'];					
					} */

					//get order status
					$arrvdate = $row['ARRVDATE'];
					$hawb_this = $row['HAWB'];
					$lateststatus = GetShipmentStatus($hawb_this,$loc,$arrvdate);
					$lstatusindex = sizeof($lateststatus) - 1;
					$statusarr[$index]['description'] = $lateststatus[$lstatusindex]['status'];
				}
				else{				
					$statusarr[$index]['description'] = $row2['DESCRIPTION'];
				}
				$index++;
				$counter++;
			}

			//use counter to check if there are rows retrieved, if none, display the default result
			if ($counter == 0){
				$statusarr[$index]['mawb'] = $const['none'];
				$statusarr[$index]['hawb'] = $hawb[$x];				
				$statusarr[$index]['arrvdate'] = $const['none'];				
				$statusarr[$index]['shiptype'] = $const['none'];
				$statusarr[$index]['description'] = $const['none_desc'];
				$index++;
			}			
		}
	}
	return $statusarr;
}

/**
 * @return array
 * @param string $hawb
 * @param string $loc
 * @param string $arrvdate
 * @desc A function that checks the details of a shipment. Returns a record set if a match is found, and returns False if no match found.
 * @author GoLivePacific
 */
function GetShipmentDetails($hawb,$loc,$arrvdate) {
	require_once('includes/config.php');
	require_once('includes/oracle_connect.php');
	require_once('includes/mysql_connect.php');
	
	$detailsarr = array();
	$index = 0;
	
	if ($hawb != '' && $hawb != NULL && $hawb != "\n" && !empty($hawb)){
		$hawb = str_replace("\n","",$hawb);		
		$sql = 'BEGIN '.$warehouse.'.chiweb_getshipmentdetails(:i_HAWB, :i_POSTINGDATE, :shipment_details); END;';
		$stmt = oci_parse($conn, $sql);
		
		// Bind the input i_HAWB argument to the $hawb PHP variable
		oci_bind_by_name($stmt,":i_HAWB",$hawb,30);
		oci_bind_by_name($stmt,":i_POSTINGDATE",$arrvdate,24);	
		
		// Create a new cursor resource
		$shipment_details = oci_new_cursor($conn);
	
		// Bind the cursor resource to the Oracle argument
		oci_bind_by_name($stmt,":shipment_details",$shipment_details,-1,SQLT_RSET);
		
		// Execute the statement
		oci_execute($stmt, OCI_DEFAULT);
		
		// Execute the cursor
		oci_execute($shipment_details);
		
		// Use fetchassoc to get rows
		$row = oci_fetch_assoc($shipment_details);

		$detailsarr[$index]['mawb'] = $row['MAWB'];
		$detailsarr[$index]['hawb'] = $row['HAWB'];				
		$detailsarr[$index]['arrvdate'] = $row['ARRVDATE'];				
		$detailsarr[$index]['weight'] = $row['WEIGHT'];
		$detailsarr[$index]['packages'] = $row['QTY'];				
		$detailsarr[$index]['description'] = $row['DESCRIPTION'];
		$index++;							
	}
	
	return $detailsarr;	
}

/**
 * @return array
 * @param string $hawb
 * @param string $loc
 * @param string $arrvdate
 * @desc A function that retrieves the status history of a shipment. Returns a record set if a match is found, and returns False if no match found.
 * @author GoLivePacific
 */
function GetShipmentStatus($hawb,$loc,$arrvdate) {
	require_once('includes/config.php');
	require_once('includes/functions.php');	
	require_once('includes/oracle_connect.php');
	require_once('includes/mysql_connect.php');
	
	$statusarr = array();
	$index = 0;
	
	if ($hawb != '' && $hawb != NULL && $hawb != "\n" && !empty($hawb)){
		$hawb = str_replace("\n","",$hawb);		
		
		//get order status
		$sql = 'BEGIN '.$warehouse.'.chiweb_getorderstatus(:i_HAWB, :i_POSTINGDATE, :status_details); END;';
		$stmt = oci_parse($conn, $sql);
		
		// Bind the input i_HAWB argument to the $hawb PHP variable
		oci_bind_by_name($stmt,":i_HAWB",$hawb,30);
		oci_bind_by_name($stmt,":i_POSTINGDATE",$arrvdate,24);	
		
		// Create a new cursor resource
		$status_details = oci_new_cursor($conn);
	
		// Bind the cursor resource to the Oracle argument
		oci_bind_by_name($stmt,":status_details",$status_details,-1,SQLT_RSET);
		
		// Execute the statement
		oci_execute($stmt, OCI_DEFAULT);
		
		// Execute the cursor
		oci_execute($status_details);
		
		// Use fetchassoc to get rows
		while ($row = oci_fetch_assoc($status_details)){
			$statusarr[$index]['postdt'] = $row['ADDDATE'];	
			/*if ($row['STATUS'] <> $const['status95']){			
				$statusarr[$index]['location'] = $const['examarea'];		
			}
			else{
				$statusarr[$index]['location'] = $const['fordelivery'];		
			}*/
			$wm9status = $row['DESCRIPTION'];
			if ($wm9status == $const['not_started']){
				$statusarr[$index]['location'] = $const['returnedarea'];			
			}
			elseif ($wm9status == $const['ship_complete']){
				$statusarr[$index]['location'] = $const['fordelivery'];			
			}
			else{
				$statusarr[$index]['location'] = $const['releasingarea'];			
			}
			$query = "SELECT SHIPMENTSTATUS.DESCRIPTION FROM SHIPMENTSTATUS WHERE WM9STATUS = '$wm9status'";
			$rs2 = mysql_query($query);
			$row2 = mysql_fetch_assoc($rs2);				
			$statusarr[$index]['status'] = $row2['DESCRIPTION'];
			$index++;						
		}
				
		//get putaway status
		//move or putaway will always have just one description for status -- Putaway; this is according to the latest table of status from CHI
		$sql = 'BEGIN '.$warehouse.'.chiweb_getputawaystatus(:i_HAWB, :i_POSTINGDATE, :status_details); END;';
		$stmt = oci_parse($conn, $sql);
		
		// Bind the input i_HAWB argument to the $hawb PHP variable
		oci_bind_by_name($stmt,":i_HAWB",$hawb,30);
		oci_bind_by_name($stmt,":i_POSTINGDATE",$arrvdate,24);	
		
		// Create a new cursor resource
		$status_details = oci_new_cursor($conn);
	
		// Bind the cursor resource to the Oracle argument
		oci_bind_by_name($stmt,":status_details",$status_details,-1,SQLT_RSET);
		
		// Execute the statement
		oci_execute($stmt, OCI_DEFAULT);
		
		// Execute the cursor
		oci_execute($status_details);
		
		// Use fetchassoc to get rows
		$row = oci_fetch_assoc($status_details);
		if (!empty($row['ADDDATE']) && $row['ADDDATE'] != NULL && $row['ADDDATE'] != ''){
			$statusarr[$index]['postdt'] = $row['ADDDATE'];				
			$statusarr[$index]['location'] = $const['storage'];		
			$wm9status = $const['mysql_move'];
			$query = "SELECT SHIPMENTSTATUS.DESCRIPTION FROM SHIPMENTSTATUS WHERE WM9STATUS = '$wm9status'";
			$rs2 = mysql_query($query);
			$row2 = mysql_fetch_assoc($rs2);				
			$statusarr[$index]['status'] = $row2['DESCRIPTION'];
			$index++;						
		}

		//get receipt status
		//according to design specs, receipt will always return ONLY ONE ROW because receipt status does not store a history of status updates
		$sql = 'BEGIN '.$warehouse.'.chiweb_getreceiptstatus(:i_HAWB, :i_POSTINGDATE, :status_details); END;';
		$stmt = oci_parse($conn, $sql);
		
		// Bind the input i_HAWB argument to the $hawb PHP variable
		oci_bind_by_name($stmt,":i_HAWB",$hawb,30);
		oci_bind_by_name($stmt,":i_POSTINGDATE",$arrvdate,24);	
		
		// Create a new cursor resource
		$status_details = oci_new_cursor($conn);
	
		// Bind the cursor resource to the Oracle argument
		oci_bind_by_name($stmt,":status_details",$status_details,-1,SQLT_RSET);
		
		// Execute the statement
		oci_execute($stmt, OCI_DEFAULT);
		
		// Execute the cursor
		oci_execute($status_details);
		
		// Use fetchassoc to get rows
		$row = oci_fetch_assoc($status_details);
		$receiptkey = $row['RECEIPTKEY'];
		$statusarr[$index]['postdt'] = $row['DATESTAMP'];				
		$statusarr[$index]['location'] = $const['stagingarea'];		
		$wm9status = $row['DESCRIPTION'];
		$query = "SELECT SHIPMENTSTATUS.DESCRIPTION FROM SHIPMENTSTATUS WHERE WM9STATUS = '$wm9status'";
		$rs2 = mysql_query($query);
		$row2 = mysql_fetch_assoc($rs2);				
		$statusarr[$index]['status'] = $row2['DESCRIPTION'];
		$index++;
		
		if ($wm9status <> $const['in_receiving']){
			//get receipt detail status if shipment is already verified
			$sql = 'BEGIN '.$warehouse.'.chiweb_getreceiptdetail(:i_RECEIPTKEY, :receipt_details); END;';
			$stmt = oci_parse($conn, $sql);
			
			oci_bind_by_name($stmt,":i_RECEIPTKEY",$receiptkey,17);
			
			// Create a new cursor resource
			$receipt_details = oci_new_cursor($conn);
		
			// Bind the cursor resource to the Oracle argument
			oci_bind_by_name($stmt,":receipt_details",$receipt_details,-1,SQLT_RSET);
			
			// Execute the statement
			oci_execute($stmt, OCI_DEFAULT);
			
			// Execute the cursor
			oci_execute($receipt_details);
			
			// Use fetchassoc to get rows
			$row = oci_fetch_assoc($receipt_details);
			$statusarr[$index]['postdt'] = $row['DATESTAMP'];				
			$statusarr[$index]['location'] = $const['stagingarea'];		
			$wm9status = $const['in_receiving'];
			$query = "SELECT SHIPMENTSTATUS.DESCRIPTION FROM SHIPMENTSTATUS WHERE WM9STATUS = '$wm9status'";
			$rs2 = mysql_query($query);
			$row2 = mysql_fetch_assoc($rs2);				
			$statusarr[$index]['status'] = $row2['DESCRIPTION'];
			$index++;		
		}
	}
	usort($statusarr, "cmp");

	return $statusarr;
}
ini_set("soap.wsdl_cache_enabled", "0"); 
$server = new SoapServer("ws_status.wsdl"); 
$server->addFunction("GetMatchedRecords");
$server->addFunction("GetShipmentDetails");
$server->addFunction("GetShipmentStatus"); 
$server->handle();
?>