<?php
/**
 * @return array
 * @param string $hawb
 * @param string $loc
 * @param string $arrvdate
 * @param string $erdate 
 * @desc A function that checks the billing details of a shipment. Returns a record set if a match is found, and returns False if no match found.
 * @author GoLivePacific
 */
function GetBillingDetails($hawb,$loc,$arrvdate, $erdate) {
	require_once('includes/config.php');
	require_once('includes/oracle_connect.php');
	require_once('includes/mysql_connect.php');
	
	//initialize variables
	$detailsarr = array();			//array containing the resultset
	$index = 0;						//used as an index for detailsarr
	
	//if house airway bill is not empty
	if ($hawb != '' && $hawb != NULL && $hawb != "\n" && !empty($hawb)){
		$hawb = str_replace("\n","",$hawb);		
		$sql = 'BEGIN '.$warehouse.'.chiweb_getbillingdetails(:i_HAWB, :i_POSTINGDATE, :i_INSTANCE, :billing_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);
		oci_bind_by_name($stmt,":i_INSTANCE",$warehouse,10);						
		
		// Create a new cursor resource
		$billing_details = oci_new_cursor($conn);
	
		// Bind the cursor resource to the Oracle argument
		oci_bind_by_name($stmt,":billing_details",$billing_details,-1,SQLT_RSET);
		
		// Execute the statement
		oci_execute($stmt, OCI_DEFAULT);
		
		// Execute the cursor
		oci_execute($billing_details);
		
		// Use fetchassoc to get rows
		$row = oci_fetch_assoc($billing_details);

		//no need to check if it exists because billing.php checks if shipment has arrived in order status
		$detailsarr[$index]['orderkey'] = $row['ORDERKEY'];
		$detailsarr[$index]['mawb'] = $row['MAWB'];
		$detailsarr[$index]['hawb'] = $row['HAWB'];	
		$detailsarr[$index]['arrvdate'] = $row['ARRVDATE'];			
		$detailsarr[$index]['flight'] = $row['FLIGHT'];
		$detailsarr[$index]['description'] = $row['DESCR'];				
		$detailsarr[$index]['weight'] = $row['TOTWGT'];
		$detailsarr[$index]['returns'] = $row['RETURNS'];
		if ($loc != $const['manila']){
			$detailsarr[$index]['cbm'] = $row['CBM'];		
		}
		else{
			$detailsarr[$index]['cbm'] = '';		
		}
		
		//compute for storage days; erdate is expected release date and arrvdate is arrive date
		//storage days is (erdate - arrvdate) + 1
		$difference = abs(strtotime($erdate) - strtotime($arrvdate));
		$storagedays = round((($difference/60)/60)/24,0);
		$detailsarr[$index]['storagedays'] = $storagedays + 1;			
	}
	
	return $detailsarr;	
}
/**
 * @return array
 * @param string $orderkey
 * @param integer $vat
 * @param integer $ewt
 * @param integer $backdoor 
 * @param string $loc
 * @param string $weight 
 * @param integer $storagedays  
 * @param string $returns 
 * @param string $hawb
 * @param string $mawb 
 * @desc A function that checks the charges of a shipment. Returns a record set of billing details.
 * @author GoLivePacific
 */
function GetCharges($orderkey,$vat,$ewt,$backdoor,$loc,$weight,$storagedays,$returns,$hawb,$mawb) {
	//oracle_connect determines the instance to be used - WH1 for Manila and WH2 for Cebu
	require_once('includes/config.php');
	require_once('includes/oracle_connect.php');
	require_once('includes/mysql_connect.php');
	
	//initialize variables
	$detailsarr = array();				//array containing the resultset
	$billdetails = array();				//array containing the billing details of a charge
	$index = 0;							//used as an index for detailsarr
	$i = 0;								//used as an index for billdetails
	$qty = 0;							//no of packages
	$flag = false;						//used as a marker for adding an additional parameter to the where clause of selecting the item class from the itemclass table
	$today = date("Y-m-d");				//today's date to be used for selecting charges
	$total = 0;							//estimated total	

	//preparing statements for execution of chiweb_getsku stored proc in oracle
	//Select the data in the VSODETAILINFO table using the orderkey retrieved from billing.php
	$sql = 'BEGIN '.$warehouse.'.chiweb_getsku(:i_ORDERKEY, :sku_val); END;';
	$stmt = oci_parse($conn, $sql);
	
	// Bind the input i_ORDERKEY argument to the $orderkey PHP variable
	oci_bind_by_name($stmt,":i_ORDERKEY",$orderkey,10);
	
	// Create a new cursor resource
	$sku_val = oci_new_cursor($conn);

	// Bind the cursor resource to the Oracle argument
	oci_bind_by_name($stmt,":sku_val",$sku_val,-1,SQLT_RSET);
	
	// Execute the statement
	oci_execute($stmt, OCI_DEFAULT);
	
	// Execute the cursor
	oci_execute($sku_val);
	
	/*SECTION 1: This section compares the item class to determine the SKU with the highest rate because this will be used for the computation*/
	
	$query = "select sku from itemclass where ";	//initialize query
	
	// Use fetchassoc to get rows; in while loop because selection of more than one record is possible
	while($row = oci_fetch_assoc($sku_val)){
		//if this is the first record
		if (!$flag){
			$query .= "sku = '".$row['SKU']."'";
		}
		//if this is NOT the first record
		else{
			$query .= " || sku = '".$row['SKU']."'";			
		}
		$qty = $row['QTY'];
		$uom = $row['UOM'];
		$flag = true;
	}
	$query .= " order by sort limit 1";				//complete the query; the query basically retrieves the SKU with the highest rate
	$rs = mysql_query($query);
	$row_sku = mysql_fetch_assoc($rs);
	$sku = $row_sku['sku'];							//SKU with highest rate is the basis for computation		

	/*End of Section 1*/	
	
	$detailsarr[$index]['qty'] = $qty." ".$uom;				//assign quantity to the qty index of detailsarr
	
	/*SECTION 2: From the retrieved item class select from app_reftab table retrieve the charges that will be available for this commodity class. */
	$query = "select element_code, element_desc, element_val from app_reftab where element_code = '$sku' and element_cat = '$warehouse' and element_name = '$mat'";
	$rs1 = mysql_query($query);
	while($row_my = mysql_fetch_assoc($rs1)){
		$elcode = $row_my['element_code'];
		$eldesc = $row_my['element_desc'];
		$chg_code = $row_my['element_val'];

		//SECTION 3: Determine if VAT, EWT and Deconsolidation Fee are to be computed
		if ($vat == 0 && $chg_code == $app_vat){
			continue;
		}
		if ($ewt == 0 && $chg_code == $app_ewt){
			continue;
		}
		if ($chg_code == $app_deconsolidation && $hawb == $mawb){
			continue;
		}
		
		/*SECTION 4: From the retrieved item class select from app_reftab table the description and charge type of this billing charge. */
		$query = "select element_desc from app_reftab where element_code = '$chg_code' and element_cat = '$warehouse' and element_name = '$tab'";
		$rs = mysql_query($query);
		$row_desc = mysql_fetch_assoc($rs);
		
		$billdetails[$i]['code'] = $chg_code;								//assign charge code to code index of billdetails
		$billdetails[$i]['desc'] = $row_desc['element_desc'];				//assign charge desc to desc index of billdetails

		//check if charge code is in rfwtbreak table to determine charge type, cannot use element_rule because storage fees are WTBREAK there when they are in fact in fixed charges
		$query = "select wt_rate from rfwtbreak where chg_code = '$chg_code'";
		$rs = mysql_query($query);
		if (mysql_num_rows($rs) > 0){
			$chargetype = $const['wtbreak'];
		}
		else{
			$chargetype = $const['fxchg'];
		}
		
		//Select the rate corresponding to the retrieved chg_code in the RFFIXCHG table
		if ($chargetype == $const['fxchg']){
			$query = "select cargo_rate, cargo_min_chg, cargo_max_chg from rffixchg where chg_code = '$chg_code' and cargo_class = '$sku' and end_validity > '$today' and whse_code = '$warehouse'";
			$rs = mysql_query($query);
			$row_my_fix = mysql_fetch_assoc($rs);
			//if charge code is a Storage Fee
			if (in_array($chg_code, $storage_arr)){
				$amt = $storagedays * $weight * $row_my_fix['cargo_rate'];
				$cargo_min_chg = $row_my_fix['cargo_min_chg'];
				$cargo_max_chg = $row_my_fix['cargo_max_chg'];
				//If the computed charge is less than the minimum charge the minimum charge will be considered
				if ($cargo_min_chg <> NULL && $cargo_min_chg <> '' && !empty($cargo_min_chg) && $cargo_min_chg > $amt){
					$billdetails[$i]['amt'] = number_format($cargo_min_chg,2,'.','');			//assign amt to billdetails
					$total += $cargo_min_chg;							//add computation to total				
				}
				//at the same time if the computed charge is greater than the maximum charge the maximum charge will be considered
				elseif ($cargo_max_chg <> NULL && $cargo_max_chg <> '' && !empty($cargo_max_chg) && $cargo_max_chg <> 0 && $cargo_max_chg < $amt){
					$billdetails[$i]['amt'] = $cargo_max_chg;			//assign amt to billdetails
					$total += $cargo_max_chg;							//add computation to total				
				}
				//if it falls within the range, just use the computed charge
				else{
					$billdetails[$i]['amt'] = $amt;			//assign amt to billdetails
					$total += $amt;							//add computation to total				
				}			
			}
			else{
				$billdetails[$i]['amt'] = $row_my_fix['cargo_rate'];			//assign amt to billdetails
				if ($chg_code <> $app_vat && $chg_code <> $app_ewt){
					$total += $row_my_fix['cargo_rate'];							//add computation to total			
				}
			}
		}
		//Select the rate corresponding to the retrieved chg_code in the RFWTBREAK table
		else{
			$query = "select wt_rate from rfwtbreak where chg_code = '$chg_code' and $weight between min_wt and max_wt and end_validity > '$today' and whse_code = '$warehouse'";
			$rs = mysql_query($query);
			$row_my_wt = mysql_fetch_assoc($rs);
			$amt = $row_my_wt['wt_rate'];
			$subtotal = 0;
			
			//perform computations for weight break charges
			//if charge code = service charge			
			if ($chg_code == $app_service){
				$subtotal = $weight * $amt;
			}
			//if charge code = returned shipment fee
			elseif ($chg_code == $app_returned && $returns != 0 && $returns != NULL && !empty($returns) && $returns <> ''){
				//this was added to accommodate change in biz rule in returned shipment fee
				//if weight is between min and max, fee is weight * returns * amt, otherwise, the fee is the amount itself
				if ($weight > $app_returned_wt_min && $weight < $app_returned_wt_max){
					$subtotal = $returns * $weight * $amt;
				}
				else{
					$subtotal = $amt;				
				}
			}
			//if charge code = special or backdoor fee			
			elseif ($backdoor == 1 && $chg_code == $app_backdoor){
				//this was added to accommodate change in biz rule in backdoor fee
				//if weight is between min and max, fee is weight * amt, otherwise, the fee is the amount itself			
				if ($weight > $app_backdoor_wt_min && $weight < $app_backdoor_wt_max){
					$subtotal = $weight * $amt;						
				}
				else{
					$subtotal = $amt;				
				}
			}
			else{
				continue;
			}
			
			$billdetails[$i]['amt'] = $subtotal;						//assign amt to billdetails
			$total += $subtotal;										//add computation to total		
		}
		$i++;															//increment billdetails index
	}
	$detailsarr[$index]['cargoclass'] = $elcode." ".$eldesc;			//assign cargoclass to detailsarr
	$detailsarr[$index]['sku'] = $elcode;			//assign cargoclass to detailsarr	
	$detailsarr[$index]['total'] = $total;								//assign total to detailsarr
	$detailsarr[$index]['billdetails'] = $billdetails;					//assign billdetails to detailsarr
	return $detailsarr;	
}
ini_set("soap.wsdl_cache_enabled", "0"); 
$server = new SoapServer("ws_billing.wsdl"); 
$server->addFunction("GetBillingDetails");
$server->addFunction("GetCharges");
$server->handle();
?>