<?php require_once("../../classes/DBConM.php"); $DB = new DBConM();

$t1_name	= trim($_POST['t1_name']);
$id_from	= (int) trim($_POST['id_from']);
$id_to		= (int) trim($_POST['id_to']);

$id 		= "";
$vin 		= "";
$agetype 	= "";
$doa 		= "";
$fxmonth 	= "";
$fxyear 	= "";
$vinpfx 	= "";

$yearDiff 	= 0;
$monthAI 	= 0;
$ageinmonths= 0;
$aoa 		= "";

$make 		= "";
$model 		= "";
$fueltype 	= "";
$capacity 	= "";
$year 		= "";
$hscode 	= "";
$msrp 		= "";
$depperc 	= "";
$fob 		= "";
$cf_n 		= "";
$payable 	= "";

echo "<br />'DUTY CALCULATION' STARTED<br />";
//prepare and bind
$updstmt = $DB->con->prepare("update `{$t1_name}` set
	atf_make = ?,
	atf_model = ?,
	atf_fueltype = ?,
	atf_capacity = ?,
	atf_year = ?,
	atf_hscode = ?,
	atf_msrp = ?,
	atf_depperc = ?,
	atf_fob = ?,
	atf_cif = ?,
	atf_dutypayable = ?,
	audstat = 'pass' where vin = ?
");
$updstmt->bind_param("ssssssssssss", $make, $model, $fueltype, $capacity, $year, $hscode, $msrp, $depperc, $fob, $cf_n, $payable, $vin);

//proceed
$res = $DB->execute("select * from `{$t1_name}` where id >= {$id_from} and id <= {$id_to} order by id");
if(!is_null($res)){
	$res = $res[0];
	while($row = $DB->nextRow($res)){
		$id 		= $row['id'];
		$vin 		= $row['vin'];
		$agetype 	= $row['agetype'];
		$doa 		= 'December-1-2018';
		$fxmonth 	= $row['fxmonth'];
		$fxyear 	= $row['fxyear'];

		//find in prefixes
		$vinpfx = substr( $vin, 0, 8 ).'*'.substr( $vin, 9, 1 );
		$Q = $DB->execute("select * from us_vinprefixes where vinprefix='{$vinpfx}' and (atm_fueltype!='' or oln_fueltype!='') and (atm_capacity>0 or oln_capacity>0 or ac_capacity>0) and freight>0 and heading!='' limit 1");
		if(!is_null($Q[0]) && ($Q[1]>0)){//found
			$R = $DB->nextRow($Q[0]);
			$ret = decode($R);
			if($ret>-1){
				$updstmt->execute();//update row
				echo "<br />D@{$id}";
			}else{
				echo "<br />E({$id}){$ret}";
			}
		}else{ echo "<br />EE({$id})"; }
	}
	$updstmt->close();
	$DB->con->close();
	echo "<br />ENDED<br />";
}

function decode($R){
	global $DB, $id, $vin, $agetype, $doa, $fxmonth, $fxyear, $ageinmonths, $vinpfx, $make, $model, $fueltype, $capacity, $year, $hscode, $msrp, $depperc, $fob, $cf_n, $payable;

	$ret = -100;
	$decres = array();

	$fueltype 	= trim( $R['atm_fueltype'] );
	$capacity 	= (double) $R['atm_capacity'];
	//$seats 		= (int) $R['atm_seats'];
	$year 		= $R['vicyear'];
	
	//checks
	$capacity = (double) $R["atm_capacity"];
	if(!$capacity>0){ $capacity = (double) $R["oln_capacity"]; }
	if(!$capacity>0){ $capacity = (double) $R["ac_capacity"]; }

	/*$seats = (int) $R["atm_seats"];
	if(!$seats>0){ $seats = (int) $R["oln_seats"]; }*/

	$fueltype = trim($R["atm_fueltype"]);
	if($fueltype==''){ $fueltype = trim($R["oln_fueltype"]); }

	$freight 		= $R['freight'];
	$heading 		= $R['heading'];
	//$photo 			= '';

	//the descriptions and bodystyles
	$T = $DB->execute("select * from us_vicdescriptions where vicmake = '{$R['vicmake']}' and vicseries = '{$R['vicseries']}' and vicbody = '{$R['vicbody']}' and vicyear = '{$R['vicyear']}'");
	if(!is_null($T[0]) && ($T[1]>0)){//found
		$T = $T[0];
		$info = $DB->nextRow($T);

		$make 			= $info['make'];
		$model 			= $info['model'];
		$makecode 		= $info['vicmake'];
		$modelcode 		= $info['modelcode'];
		$seriescode 	= $info['vicseries'];
		$bodycode 		= $info['vicbody'];

		//result
		/*$decres['vin'] 		= $vin;
		$decres['myear'] 	= $R['vicyear'];
		$decres['make'] 	= $R['make'];
		$decres['model'] 	= $R['model'];
		$decres['capacity'] = $capacity;*/
		//$decres['freight'] 	= $freight;
		//$decres['heading'] 	= $heading;
		//$decres['series']  	= $info["series"];
		//$decres['trim'] 		= $info["series"];
		//$decres['engine'] 	= $info["engine"];
		//$decres['fueltype'] 	= $fueltype;
		//$decres['seats'] 		= $seats;
		//$decres['doors'] 		= $info["doors"];
		//$decres['weight'] 	= (double) $info["weight"];
		//$decres['coomanuf'] 	= $info["country"];
		//$decres['coorigin'] 	= 'U.S.A';
		//$decres['photo'] 		= $photo;
		
		//the bodystyles
		//$bstyles = array();
		$U = $DB->execute("select * from us_vicdescriptions where modelcode='{$modelcode}' and vicmake='{$R['vicmake']}' and vicseries='{$R['vicseries']}' and vicyear='{$R['vicyear']}' order by msrp ASC");
		if(!is_null($U[0]) && ($U[1]>0)){
			$U = $U[0];
			$W = $DB->nextRow($U);

			$msrp = $W["msrp"];

			//calculate duty
			$S = array(); $rs = array();

			//HEADING
			/*$S['myear'] 	= $decres['myear'];
			$rs['year'] 	= $S['myear'];

			$S['make'] 		= $decres['make'];
			$rs['make'] 	= $S['make'];

			$S['model'] 	= $decres['model'];
			$rs['model'] 	= $S['model'];*/

			/*$S['bodystyle'] = $decres['bodystyle'];
			$rs['bodystyle'] = $S['bodystyle'];

			$S['trim'] 		= $decres['trim'];
			$rs['trim'] 	= $S['trim'];

			$S['title'] 	= $S['myear'].' '.$S['make'].' '.$S['model'].' '.$S['bodystyle'].' '.$S['trim'];
			$rs['title'] 	= $S['title'];

			//PRE-BODY
			$S['photo'] 	= $decres['photo'];
			$rs['photo'] 	= $S['photo'];

			$S['coorigin'] 	= "USA";
			$rs['coorigin'] = $S['coorigin'];

			$S['refid'] 	= strtoupper("US".mt_rand(100,999).substr(uniqid(),9));
			$rs['refid'] 	= $S['refid'];

			$S['transactdate'] 	= date('M d, Y H:i:s');
			$rs['transactdate'] = $S['transactdate'];*/

			//VEHICLE SPECIFICATION
			/*$S['vin'] 		= $decres['vin'];
			$rs['vin'] 		= $S['vin'];

			$S['seats'] 	= $decres['seats'];
			$rs['seats'] 	= $S['seats'];

			$S['doors'] 	= $decres['doors'];
			$rs['doors']	= $S['doors'];

			$S['fueltype'] 	= $decres['fueltype'];
			$rs['fueltype'] = ucfirst($S['fueltype']);

			$S['capacity'] 	= $decres['capacity'];
			$rs['capacity'] = $S['capacity'].' ccm';

			$S['engine'] 	= $decres['engine'];
			$rs['engine'] 	= $S['engine'];

			$S['coomanuf'] 	= $decres['coomanuf'];
			$rs['coomanuf'] = $S['coomanuf'];

			$S['measurement'] 	= 'NA';
			$rs['measurement'] 	= $S['measurement'];

			$S['weight'] 	= $decres['weight'];
			$rs['weight'] 	= $S['weight'];

			$S['production'] 	= 'NA';
			$rs['production'] 	= $S['production'];*/

			/*$S['doa'] 	= $this->vininfo[$decres['vin']]['doa'];
			$rs['doa'] 	= $S['doa'];*/

			getAgeOnArrival( $doa, $year );

			/*$S['aoa'] 	= $this->aoa;
			$rs['aoa'] 	= $S['aoa'];

			$heading = $decres['heading'];;*/
			//get hs applicabbles
			//but we know it's 8703s
			$cond = " and fueltype = '{$fueltype}' and (capacityfrom <= {$capacity} and capacityto >= {$capacity})";
			$qry = "select * from hs_coding where age='{$agetype}' and heading='{$heading}'{$cond}";

			$X = $DB->execute($qry);
			if(!is_null($X[0]) && ($X[1]>0)){
				$Y = $DB->nextRow($X[0]);

				$S['cisperc'] 	= $Y['cis'];
				$rs['cisperc' ] = formatPerc( $S['cisperc'] );

				$S['dtyperc'] 	= $Y['dty'];
				$rs['dtyperc'] 	= formatPerc( $S['dtyperc'] );

				$S['nacperc'] 	= $Y['nac'];
				$rs['nacperc'] 	= formatPerc( $S['nacperc'] );

				$S['surperc'] 	= $Y['sur'];
				$rs['surperc'] 	= formatPerc( $S['surperc'] );

				$S['etlperc'] 	= $Y['etl'];
				$rs['etlperc'] 	= formatPerc( $S['etlperc'] );

				$S['vatperc'] 	= $Y['vat'];
				$rs['vatperc'] 	= formatPerc( $S['vatperc'] );

				$hscode 		= $Y['hscode'];

				/*$S['hscode'] 	= $Y['hscode'];
				$rs['hscode'] 	= $S['hscode'];*/
			}else{
				$ret = -1; return $ret;
			}

			//COST AND FREIGHT
			$S['msrp'] 			= (double) $msrp;
			$rs['msrp'] 		= format( $S['msrp'] );

			//depreciation
			if( $agetype == "new" ){
				$S['depperc'] = 0;
			}else{
				$X = $DB->execute("select * from depreciation where periodfrom<={$ageinmonths} and periodto>={$ageinmonths}");
				if(!is_null($X[0]) && ($X[1]>0)){
					$Y = $DB->nextRow($X[0]);
					$S['depperc'] = $Y['depreciationp'];
				}else{
					$ret = -2; return $ret;
				}
			}
			$rs['depperc' ] = formatPerc( $S['depperc'] );
			$depperc 		= $rs['depperc' ];

			$S['fob'] 		= ( 1 - ( ( (double) $S['depperc'] ) / 100 ) ) * $S['msrp'];
			$rs['fob'] 		= format( $S['fob'] );
			$fob 			= $rs['fob'];

			$S['freight']		= (double) $decres['freight'];
			$rs['freight'] 		= format( $S['freight'] );

			$S['cf_f'] 			= $S['fob'] + $S['freight'];
			$rs['cf_f'] 		= format( $S['cf_f'] );

			$S['currency'] 		= "USD";
			$rs['currency'] 	= $S['currency'];

			//get fx rates
			$X = $DB->execute("select rate from fx_rates_nig where year = '{$fxyear}' and month = '{$fxmonth}' limit 1");
			if(!is_null($X[0]) && ($X[1]>0)){
				$Y = $DB->nextRow($X[0]);
				$S['fxrate'] = $Y['rate'];
			}
			$rs['fxrate'] 		= formatRate( $S['fxrate'] );

			$S['cf_n'] 			= $S['cf_f'] * $S['fxrate'];
			$rs['cf_n'] 		= format( $S['cf_n'] );
			$cf_n 				= $rs['cf_n'];

			//DUTY, TAXES AND LEVIES
			$S['cisbase'] 	= $S['cf_n'];
			$rs['cisbase'] 	= format( $S['cisbase'] );

			$S['dtybase'] 	= $S['cf_n'];
			$rs['dtybase'] 	= format( $S['dtybase'] );

			$S['nacbase'] 	= $S['cf_n'];
			$rs['nacbase'] 	= format( $S['nacbase'] );

			$S['surbase'] 	= ( $S['dtyperc'] / 100 ) * $S['dtybase'];
			$rs['surbase'] 	= format( $S['surbase'] );

			$S['etlbase'] 	= $S['cf_n'];
			$rs['etlbase'] 	= format( $S['etlbase'] );

			//do some things first
			$S['cisamt'] 	= ( $S['cisperc'] / 100 ) * $S['cisbase'];
			$rs['cisamt'] 	=format( $S['cisamt'] );

			$S['dtyamt'] 	= ( $S['dtyperc'] / 100 ) * $S['dtybase'];
			$rs['dtyamt'] 	=format( $S['dtyamt'] );

			//NATHERE
			$S['nacamt'] 	= ( $S['nacperc'] / 100 ) * $S['nacbase'];
			$rs['nacamt'] 	= format( $S['nacamt'] );

			$S['suramt'] 	= ( $S['surperc'] / 100 ) * $S['surbase'];
			$rs['suramt'] 	= format( $S['suramt'] );

			$S['etlamt'] 	= ( $S['etlperc'] / 100 ) * $S['etlbase'];
			$rs['etlamt'] 	= format( $S['etlamt'] );

			//carry on now
			$S['vatbase'] 	= $S['cisbase'] + ( $S['dtyamt'] + $S['nacamt'] + $S['suramt'] + $S['etlamt'] );
			$rs['vatbase'] 	= format( $S['vatbase'] );

			$S['vatamt'] 	= ( $S['vatperc'] / 100 ) * $S['vatbase'];
			$rs['vatamt'] 	= format( $S['vatamt'] );		

			//TOTAL
			$S['payable'] 	= $S['cisamt'] + $S['dtyamt'] + $S['nacamt'] + $S['suramt'] + $S['etlamt'] + $S['vatamt'];
			$rs['payable'] 	= format( $S['payable'] );
			$payable = $rs['payable'];

			$ret = 0;
		}
	}
	return $ret;
}

function format($d){
	return number_format((double) $d,0,".",",");
}
function formatPerc($d){
	return number_format(((double) $d),0,".",",")."%";
}
function formatRate($d){
	return number_format((double) $d,4,".",",");
}

function getAgeOnArrival($doa, $year){
	global $yearDiff, $monthAI, $ageinmonths, $aoa;
	
	$tmp			= explode( '-', $doa );
	$arrivalyear	= (int) trim( $tmp[2] );
	$modelyear		= (int) $year;
	$curyear		= (int) date("Y");
	
	$yearDiff = $arrivalyear - $modelyear;
	if( $yearDiff >= 10 ) $yearDiff = $yearDiff - 1;
	
	$monthAI = monthAI( trim( $tmp[0] ) ); $ageinmonths = 0;
	
	if( $modelyear <= $curyear ){
		if( $arrivalyear >= $modelyear ){ calculateAgeNormally(); }
		else{
			$aoa="Incorrect selection - The estimated year of arrival cannot be earlier than the year of manufacture";
		}
	}else{//make year zero and add the month value for calculation
		if( $arrivalyear >= $modelyear ){ calculate_age_normally(); }
		else if( $arrivalyear == $curyear ){//3 months /*$this->aoa="3 mth"; $this->ageinmonths=3;*/
			$aoa = "{$monthAI} mth";
			$ageinmonths = $monthAI;
		}else if( $arrivalyear < $curyear ){
			$aoa = "Incorrect selection - The estimated year of arrival cannot be earlier than the year of manufacture";
		}else{
			$aoa = "Unknown Case - An error occurred while trying to calculate the age";
		}
	}
}
function calculateAgeNormally(){
	global $yearDiff, $monthAI, $ageinmonths, $aoa;

	if( $monthAI == 12 ){
		$yearDiff = $yearDiff + 1; $monthAI = 0;
	}
	if( $monthAI > 0 ){
		$aoa = "{$yearDiff} yr {$monthAI} mth";
	} else{
		$aoa = "{$yearDiff} yr";
	}
	$ageinmonths = ( $yearDiff * 12 ) + $monthAI;
}
function monthAI( $str ){
	$n = -1;
	if($str == "January") 		$n = 1;
	else if($str == "February") $n = 2;
	else if($str == "March") 	$n = 3;
	else if($str == "April") 	$n = 4;
	else if($str == "May") 		$n = 5;
	else if($str == "June") 	$n = 6;
	else if($str == "July") 	$n = 7;
	else if($str == "August") 	$n = 8;
	else if($str == "September") $n = 9;
	else if($str == "October") 	$n = 10;
	else if($str == "November") $n = 11;
	else if($str == "December") $n = 12;
	else $n = -1;
	return $n;
}
?>