<?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(); getAgeOnArrival( $doa, $year );

			//HEADING
			$S['myear'] 	= $this->input->post('myear', true);
			$S['make'] 		= trim( $this->input->post('make', true) );
			$S['model'] 	= trim( $this->input->post('model', true) );
			$S['bodystyle'] = trim( $this->input->post('bodystyle', true) );
			$S['trim'] 		= trim( $this->input->post('trim', true) );

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

			//PRE-BODY
			$S['photo'] = $this->input->post('photo', true);
			$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'] 	= $this->input->post('vin', true);
			$rs['vin'] = $S['vin'];

			$S['seats'] 	= $this->input->post('seats', true);
			$rs['seats'] = $S['seats'];

			$S['doors'] 	= $this->input->post('doors', true);
			$rs['doors'] = $S['doors'];

			$S['fueltype'] 	= strtolower( $this->input->post('fueltype', true) );
			$rs['fueltype'] = ucfirst($S['fueltype']);

			$S['capacity'] 	= $this->input->post('enginecapacity', true);
			$rs['capacity'] = $S['capacity'].' ccm",';

			$S['engine'] 	= $this->input->post('engine', true);
			$rs['engine'] = $S['engine'];

			$S['coomanuf'] 	= $this->input->post('coomanuf', true);
			$rs['coomanuf'] = $S['coomanuf'];

			$S['measurement'] 	= $this->input->post('measurement', true);
			$rs['measurement'] = $S['measurement'];

			$S['weight'] 	= $this->input->post('weight', true);
			$rs['weight'] = $S['weight'];

			$S['production'] 	= $this->input->post('production', true);
			$rs['production'] = $S['production'];

			$S['doa'] 	= $this->input->post('doa', true);
			$rs['doa'] = $S['doa'];

			$S['aoa'] 	= $this->input->post('aoa', true);
			$rs['aoa'] = $S['aoa'];

			$S['hscode'] = "N/A";

			$heading = $this->input->post('heading', true);
			//get hs applicabbles
			$cond = '';
			$res = $this->db->query("select * from hs_applicables where heading='{$heading}'");
			if($res->num_rows()>0){
				$row = $res->result_array()[0];

				if($row['fueltype'] == '*')
				$cond .= " and fueltype = '{$this->fueltype}'";

				if($row['capacity'] == '*')
				$cond .= " and (capacityfrom <= {$this->capacity} and capacityto >= {$this->capacity})";

				if($row['seats'] == '*')
				$cond .= " and (seatfrom <= {$this->seats} and seatto >= {$this->seats})";

				if($row['weight'] == '*')
				$cond .= " and (tonnesfrom <= {$this->weight} and tonnesto >= {$this->weight})";
			}

			$agetype = $this->input->post('agetype', true);
			switch(strtolower($S['fueltype'])){
				case "electric": case "hybrid": case "gas":
				$qry = "select * from hs_coding_gam where fueltype='{$S['fueltype']}'";
				break;

				default:
				$qry = "select * from hs_coding_gam where age='{$agetype}' and heading='{$heading}'{$cond}";
			}
			$res = $this->db->query($qry);
			if($res->num_rows()>0){
				$row = $res->result_array()[0];
				$S['dtyperc'] = $row['importduty'];
				$rs['dtyperc'] = $this->formatPerc( $S['dtyperc'] );

				$S['vatperc'] = $row['vat'];
				$rs['vatperc'] = $this->formatPerc( $S['vatperc'] );

				$S['excperc'] = $row['excise'];
				$rs['excperc'] = $this->formatPerc( $S['excperc'] );

				$S['auperc'] = $row['au'];
				$rs['auperc'] = $this->formatPerc( $S['auperc'] );

				$S['prcperc'] = $row['processing'];
				$rs['prcperc'] = $this->formatPerc( $S['prcperc'] );

				$S['ecwperc'] = $row['ecowas'];
				$rs['ecwperc'] = $this->formatPerc( $S['ecwperc'] );

				$S['envperc'] = $row['environmental'];
				$rs['envperc'] = $this->formatPerc( $S['envperc'] );

				$S['hscode'] = $row['hscode'];
				$rs['hscode'] = $S['hscode'];
			}else{
				return "-1";
			}

			//COST AND FREIGHT
			$S['msrp'] = (double) $this->input->post('msrp', true);
			$rs['msrp'] = $this->format( $S['msrp'] );

			//depreciation
			if(trim(strtolower($this->input->post('agetype', true))) == "new"){
				$S['depperc'] = 0;
			}else{
				$res=$this->db->query("select * from depreciation_gam where periodfrom<={$this->ageinmonths} and periodto>={$this->ageinmonths}");
				if($res->num_rows()>0){
					$row = $res->result_array()[0];
					$S['depperc'] = $row['depreciationp'];
				}else{
					return "-2";
				}
			}
			$rs['depperc'] = $this->formatPerc( $S['depperc'] );

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

			$S['freight'] = (double) $this->input->post('freight', true);
			$rs['freight'] = $this->format( $S['freight'] );

			$S['cf_f'] = $S['residue'] + $S['freight'];
			$rs['cf_f'] = $this->format( $S['cf_f'] );

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

			//get fx rates
			$curyear = date('Y');
			$curmonth = date('M');

			$Q = $this->db->query("select rate from fx_rates_gam where year <= '{$curyear}' and month = '{$curmonth}' order by year DESC limit 1");
			if($Q->num_rows()>0){ $R = $Q->result_array()[0]; $S['fxrate'] = $R['rate']; }

			$rs['fxrate'] = $this->formatRate( $S['fxrate'] );

			$S['cf_l'] = $S['cf_f'] * $S['fxrate'];
			$rs['cf_l'] = $this->format( $S['cf_l'] );

			//DUTY, TAXES AND LEVIES
			$S['dtybase'] = $S['cf_l'];
			$rs['dtybase'] = $this->format( $S['dtybase'] );

			$S['prcbase'] = $S['cf_l'];
			$rs['prcbase'] = $this->format( $S['prcbase'] );

			$S['ecwbase'] = $S['cf_l'];
			$rs['ecwbase'] = $this->format( $S['ecwbase'] );

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

			$S['prcamt'] = ( $S['prcperc'] / 100 ) * $S['prcbase'];
			$rs['prcamt'] = $this->format( $S['prcamt'] );

			$S['ecwamt'] = ( $S['ecwperc'] / 100 ) * $S['ecwbase'];
			$rs['ecwamt'] = $this->format( $S['ecwamt'] );

			//continues
			$S['excbase'] = $S['cf_l'] + $S['dtybase'] + $S['prcbase'] + $S['ecwbase'];
			$rs['excbase'] = $this->format( $S['excbase'] );

			//amount
			$S['excamt'] = ( $S['excperc'] / 100 ) * $S['excbase'];
			$rs['excamt'] = $this->format( $S['excamt'] );

			//continues
			$S['vatbase'] = $S['cf_l'] + $S['dtybase'] + $S['excbase'];
			$rs['vatbase'] = $this->format( $S['vatbase'] );

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

			//continues
			$S['aubase'] = $S['cf_l'];
			$rs['aubase'] = $this->format( $S['aubase'] );

			//amount
			$S['auamt'] = ( $S['auperc'] / 100 ) * $S['aubase'];
			$rs['auamt'] = $this->format( $S['auamt'] );

			//continues
			$S['envbase'] = "";
			$rs['envbase'] = "";

			//amount
			$S['envamt'] = $S['envperc'];
			$rs['envamt'] = $this->format( $S['envamt'] );		

			//TOTAL
			$S['payable'] =  $S['dtyamt'] + $S['vatamt'] + $S['excamt'] + $S['auamt'] + $S['prcamt'] + $S['ecwamt'] + $S['envamt'];

			$rs['payable'] = $this->format( $S['payable'] );

			$S['machineip']	= $_SERVER['REMOTE_ADDR'];

			$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;
}
?>