<?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 			= "";
$cif 			= "";
$payable 		= "";

if(!($id_from > 0 && $id_to > 0 && $id_to >= $id_from)) die("Error... Check the Start and End points!");

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, $cif, $payable, $vin);*/
$updstmt = $DB->con->prepare("update `{$t1_name}` set
	atf_hscode = ?,
	atf_msrp = ?,
	atf_depperc = ?,
	atf_fob = ?,
	atf_cif = ?,
	atf_dutypayable = ?,
	audstat = 'pass' where vin = ?
");
$updstmt->bind_param("sssssss", $hscode, $msrp, $depperc, $fob, $cif, $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($R = $DB->nextRow($res)){
		$id 		= $R['id'];
		$vin 		= $R['vin'];
		$agetype 	= $R['agetype'];
		$doa 		= $R['date_on_arrival'];//'December-1-2018';
		$fxmonth 	= $R['fxmonth'];
		$fxyear 	= $R['fxyear'];
		$msrp 		= $R['atf_msrp'];

		switch($R['country_of_supply']){
			case "US":
			$ret = decodeUS($R);
			break;

			case "EU":
			$ret = decodeEU($R);
			break;
		}

		if($ret>-1){
			$updstmt->execute();//update row
			echo "<br />D@{$id}";
		}else{
			echo "<br />E({$id}){$ret}";
		}
	}
	$updstmt->close();
	$DB->con->close();
	echo "<br />ENDED<br />";
}

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

	$S = array(); $rs = array();

	getAgeOnArrival( $doa, $R['year'] );

	$X = $DB->execute("select depreciationcode, freightcode, dutycode from tax_relationships where code = '{$R['category']}' and ((seatfrom <= {$R['seats']} and seatto >= {$R['seats']}) or (seatfrom = 'x' and seatto = 'x'))");
	if(!is_null($X[0]) && ($X[1]>0)){
		$Y = $DB->nextRow($X[0]);

		$depcode			= trim($Y['depreciationcode']);
		$freightcode 		= trim($Y['freightcode']);
		$dutycode 			= trim($Y['dutycode']);
		
		if($depcode == '' || $freightcode == '' || $dutycode == ''){
			return '-95';
		}
	}else{
		return '-100';
	}

	//GET PENALTY AND DEPRECIATION PERCENTAGES
	$penaltyp = 0; $agetype = '';
	$X = $DB->execute("select * from depreciation_gha where code='{$depcode}' and periodfrom <= {$ageinmonths} and periodto >= {$ageinmonths}");
	if(!is_null($X[0]) && ($X[1]>0)){
		$Y = $DB->nextRow($X[0]);

		$S['depperc'] 		= ((double) $Y['depreciationp'])/100;
		$rs['depperc'] = formatPerc($S['depperc']);

		$depperc = $rs['depperc'];

		$S['penaltyperc'] 	= ((double) $Y['penaltyp'])/100;
		$rs['penaltyperc'] = formatPerc($S['penaltyperc']);

		$S['agetype'] = $Y['age'];
		$rs['agetype'] = $S['agetype'];

	}else{
		return '-90';
	}
	
	//GET THE FREIGHT
	$X = $DB->execute("select * from freight where code = '{$freightcode}' and capacityfrom <= {$R['capacity']} and capacityto >= {$R['capacity']}");
	if(!is_null($X[0]) && ($X[1]>0)){
		$Y = $DB->nextRow($X[0]);

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

	}else{
		return '-85';
	}
	
	//GET THE DUTY PERCENTAGE AND HSCODE
	//what really applies? Get hs applicables first
	/*$S['heading'] = $heading;
	$rs['heading'] = $S['heading'];*/

	$cond = '';
	$X = $DB->execute("select * from hs_applicables where heading='{$R['heading']}'");
	if(!is_null($X[0]) && ($X[1]>0)){
		$Y = $DB->nextRow($X[0]);

		if($Y['fueltype'] == '*')
		$cond .= " and fueltype = '{$R['fueltype']}'";

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

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

		if($Y['weight'] == '*')
		$cond .= " and (tonnesfrom <= {$R['weight']} and tonnesto >= {$R['weight']})";
	}
	switch($ft){
		case "electric": case "hybrid": case "gas":
		$X = "select * from hs_coding_gha where fueltype='{$R['fueltype']}'";
		break;

		default:
		$X = "select * from hs_coding_gha where age = '{$R['agetype']}' and heading = '{$R['heading']}'{$cond}";
	}
	
	$X = $DB->execute($X);
	if(!is_null($X[0]) && ($X[1]>0)){
		$Y = $DB->nextRow($X[0]);

		$S['idutyperc'] = ((double) $Y['dty'])/100;
		$rs['idutyperc'] = formatPerc($S['idutyperc']);

		$S['specialtaxperc'] = (double) $Y['specialtax'];
		$rs['specialtaxperc'] = formatPerc($S['specialtaxperc']);

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

		$S['nhilperc'] = $Y['nhil'];
		$rs['nhilperc'] = formatPerc($S['nhilperc']);

		$S['hscode'] = $Y['hscode'];
		$rs['hscode'] = $S['hscode'];

		$hscode = $rs['hscode'];

	}else{
		return '-75';
	}

	//CARRY ON
	$S['msrp_actual'] 	= $msrp;
	$rs['msrp_actual'] = format($S['msrp_actual']);

	//the 30% reduction
	$S['msrp'] 	= $S['msrp_actual'] * 0.7;
	$rs['msrp'] = format($S['msrp']);

	$msrpAD = (double) $S['msrp'];

	$S['depa'] 	= $S['depperc'] * $msrpAD;
	$rs['depa'] = format($S['depa']);

	$S['fob'] 	= $msrpAD - $S['depa'];
	$rs['fob'] = format($S['fob']);

	$fob = $rs['fob'];

	$S['cf'] 	= $S['fob'] + $S['freight'];
	$rs['cf'] = format($S['cf']);
	
	//GET APPLICABILITY OF TAXES
	$X = $DB->execute("select * from tax_applicables where catcode = '{$R['category']}' and periodfrom <= {$ageinmonths} and periodto >= {$ageinmonths} and seatfrom <= {$R['seats']} and seatto >= {$R['seats']}");
	if(!is_null($X[0]) && ($X[1]>0)){
		$Y = $DB->nextRow($X[0]);

		$LA = $Y;//LA - levy applicable		
	}else{
		return '-70';
	}
	
	//GET FXRATE
	$q1 = "select rate from vin_fx_rates where symbol='USD' and vin='{$R['vin']}' limit 1";
	$q2 = "select rate from fx_rates where symbol='USD' order by periodend DESC limit 1";

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

		$S['fxrate'] = $Y['rate'];
		$rs['fxrate'] = formatRate($S['fxrate']);
	}else{
		return '-65';
	}

	//FETCH THE LEVIES
	if( $LA['insurance'] == 'YES' ){ $levy = getLevy("INSURANCE"); } else{ $levy = 0.0; }
	$S['insurance'] = $S['cf'] * $levy;
	$rs['insurance'] = format($S['insurance']);

	$S['cif'] = $S['insurance'] + $S['cf'];
	$rs['cif'] = format($S['cif']);

	$cif = $rs['cif'];

	$S['cifincedis'] = $S['cif'] * $S['fxrate'];
	$rs['cifincedis'] = format($S['cifincedis']);

	$S['iduty'] = $S['cifincedis'] * $S['idutyperc'];
	$rs['iduty'] = format($S['iduty']);

	$S['specialtax'] = $S['cifincedis'] * $S['specialtaxperc'];
	$rs['specialtax'] = format($S['specialtax']);

	$S['overagepen'] = $S['cifincedis'] * $S['penaltyperc'];
	$rs['overagepen'] = format($S['overagepen']);

	//GET "GET" FIRST, THEN NHIL AND VAT 
	if( $LA['getfund'] == 'YES' ){ $levy = getLevy("GET"); } else{ $levy = 0.0; }
	$S['get'] = ($S['cifincedis'] + $S['iduty']) * $levy;
	$rs['get'] = format($S['get']);

	$S['nhil'] = ($S['cifincedis'] + $S['iduty']) * $S['nhilperc'];
	$rs['nhil'] = format($S['nhil']);

	$S['vat'] = ($S['cifincedis'] + $S['iduty'] + $S['nhil'] + $S['get']) * $S['vatperc'];
	$rs['vat'] = format($S['vat']);

	//THE REST
	if( $LA['interest'] == 'YES' ){ $levy = getLevy("INTEREST"); } else{ $levy = 0.0; }
	$S['intcharges'] = ($S['iduty'] + $S['vat']) * $levy;
	$rs['intcharges'] = format($S['intcharges']);
	
	if( $LA['ecowas'] == 'YES' ){ $levy = getLevy("ECOWAS"); } else{ $levy = 0.0; }
	$S['ecowas'] = $S['cifincedis'] * $levy;
	$rs['ecowas'] = format($S['ecowas']);
	
	if( $LA['exim'] == 'YES' ){ $levy = getLevy("EXIM"); } else{ $levy = 0.0; }
	$S['exim'] = $S['cifincedis'] * $levy;
	$rs['exim'] = format($S['exim']);

	if( $LA['exam'] == 'YES' ){ $levy = getLevy("EXAM"); } else{ $levy = 0.0; }
	$S['examfee'] = $S['cifincedis'] * $levy;
	$rs['examfee'] = format($S['examfee']);

	if( $LA['network'] == 'YES' ){ $levy = getLevy("NETWORK"); } else{ $levy = 0.0; }
	$S['netwcharges'] = ($S['fob'] * $S['fxrate']) * $levy;
	$rs['netwcharges'] = format($S['netwcharges']);
	
	$S['vatnet'] = $S['netwcharges'] * $S['vatperc'];
	$rs['vatnet'] = format($S['vatnet']);

	$S['nhilnet'] = $S['netwcharges'] * $S['nhilperc'];
	$rs['nhilnet'] = format($S['nhilnet']);

	if( $LA['irs'] == 'YES' ){ $levy = getLevy("IRS"); } else{ $levy = 0.0; }
	$S['irs'] = $S['cifincedis'] * $levy;
	$rs['irs'] = format($S['irs']);

	if( $LA['au'] == 'YES' ){ $levy = getLevy("AU"); } else{ $levy = 0.0; }
	$S['au'] = $S['cifincedis'] * $levy;
	$rs['au'] = format($S['au']);

	if( $LA['processing'] == 'YES' ){ $levy = getLevy("PROCESS"); } else{ $levy = 0.0; }
	$S['processfee'] = $S['cifincedis'] * $levy;
	$rs['processfee'] = format($S['processfee']);
	
	if( $LA['inspection'] == 'YES' ){ $levy = getLevy("INSPECTION"); } else{ $levy = 0.0; }
	$S['inspectfee'] = $S['cifincedis'] * $levy;
	$rs['inspectfee'] = format($S['inspectfee']);
	
	if( $LA['shippers'] == 'YES' ){ $levy = getFixedLevy("SHIPPERS"); } else{ $levy = 0.0; }
	$S['shippers'] = $levy;
	$rs['shippers'] = format($S['shippers']);

	if( $LA['certification'] == 'YES' ){ $levy = getFixedLevy("CERTIFICATION"); } else{ $levy = 0.0; }
	$S['cert'] = $levy;
	$rs['cert'] = format($S['cert']);

	//TOTAL
	$S['payable'] =  $S['iduty'] + $S['specialtax'] + $S['vat'] + $S['nhil'] + $S['get'] + $S['ecowas'] + $S['exim'] + $S['examfee'] + $S['shippers'] + $S['cert'] + $S['irs'] + $S['au'] + $S['processfee'] + $S['inspectfee'] + $S['intcharges'] + $S['netwcharges'] + $S['vatnet'] + $S['nhilnet'] + $S['overagepen'];
	$rs['payable'] = format( $S['payable'] );

	$payable = $rs['payable'];

	$S['payable_usd'] =  $S['payable'] / $S['fxrate'];
	$rs['payable_usd'] = format( $S['payable_usd'] ).'"';

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

	$ret = 0;
	return $ret;
}

function format($d){
	return number_format((double) $d,2,".",",");
}
function formatPerc($d){
	return number_format((((double) $d)*100),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;
}

function getUSCategory($makecode,$modelcode,$year){
	global $DB;

	$X = $DB->execute("select category from categorization where vicmake='{$makecode}' and vicseries='{$modelcode}' and category!='' limit 1");
	if(!is_null($X[0]) && ($X[1]>0)){
		$Y = $DB->nextRow($X[0]);

		return trim($Y['category']);
	}else{
		return "";
	}
}

function getLevy($p){
	global $DB;

	$X = $DB->execute("select levy from taxes where description='{$p}'");
	if(!is_null($X[0]) && ($X[1]>0)){
		$Y = $DB->nextRow($X[0]);

		$levy = ((double) $Y['levy'])/100;
	}else{
		$genstatinfo="Levy not found: {$p}";
		$levy = 0;
	}
	return $levy;
}
function getFixedLevy($p){
	global $DB;

	$X = $DB->execute("select levy from fixed_levies where description='{$p}'");
	if(!is_null($X[0]) && ($X[1]>0)){
		$Y = $DB->nextRow($X[0]);

		$levy = $Y['levy'];
	}else{
		$genstatinfo="Fixed levy not found: {$p}";
		$levy = 0;
	}
	return $levy;
}

function findInDirectors($vin){
	$areas = array();
	$fidres = array("found"=>false, "module"=>"", "region"=>"", "vtype"=>"", "vdsplus"=>"", "vin4"=>"");
	$vin = str_replace($this->lookfor, $this->replace, strtoupper($vin));
	
	if($preference==""){//look for all
		//begin in DIRECTOR
		$modulefound = false;
		$vin15 = substr($vin,0,15); $vin15ast = "***".substr($vin,3,12); $vin15ast_m1 = "***".substr($vin,3,11)."*";
		$vin15ast_m2 = "***".substr($vin,3,10)."**";

		$vin14 = substr($vin,0,14); $vin14ast = "***".substr($vin,3,11); $vin14ast_m1 = "***".substr($vin,3,10)."*";
		$vin14ast_m2 = "***".substr($vin,3,9)."**";

		$vin13 = substr($vin,0,13); $vin13ast = "***".substr($vin,3,10); $vin13ast_m1 = "***".substr($vin,3,9)."*";
		$vin13ast_m2 = "***".substr($vin,3,8)."**";

		$vin12 = substr($vin,0,12); $vin12ast = "***".substr($vin,3,9); $vin12ast_m1 = "***".substr($vin,3,8)."*";
		$vin12ast_m2 = "***".substr($vin,3,7)."**";

		$vin11 = substr($vin,0,11); $vin11ast = "***".substr($vin,3,8); $vin11ast_m1 = "***".substr($vin,3,7)."*";
		$vin11ast_m2 = "***".substr($vin,3,6)."**";

		$vin10 = substr($vin,0,10); $vin10ast = "***".substr($vin,3,7); $vin10ast_m1 = "***".substr($vin,3,6)."*";
		$vin10ast_m2 = "***".substr($vin,3,5)."**";

		$vin9  = substr($vin,0,9);  $vin9ast  = "***".substr($vin,3,6); $vin9ast_m1  = "***".substr($vin,3,5)."*";
		$vin9ast_m2  = "***".substr($vin,3,4)."**";

		$vin8  = substr($vin,0,8);  $vin8ast  = "***".substr($vin,3,5); $vin8ast_m1  = "***".substr($vin,3,4)."*";
		$vin8ast_m2  = "***".substr($vin,3,3)."**";

		$vin7  = substr($vin,0,7);  $vin7ast  = "***".substr($vin,3,4); $vin7ast_m1  = "***".substr($vin,3,3)."*";
		$vin7ast_m2  = "***".substr($vin,3,2)."**";
		
		$qry = "select * from director where
			(len=15 and (vdsplus='{$vin15}' or vdsplus='{$vin15ast}' or vdsplus='{$vin15ast_m1}' or vdsplus='{$vin15ast_m2}')) or
			(len=14 and (vdsplus='{$vin14}' or vdsplus='{$vin14ast}' or vdsplus='{$vin14ast_m1}' or vdsplus='{$vin14ast_m2}')) or
			(len=13 and (vdsplus='{$vin13}' or vdsplus='{$vin13ast}' or vdsplus='{$vin13ast_m1}' or vdsplus='{$vin13ast_m2}')) or
			(len=12 and (vdsplus='{$vin12}' or vdsplus='{$vin12ast}' or vdsplus='{$vin12ast_m1}' or vdsplus='{$vin12ast_m2}')) or
			(len=11 and (vdsplus='{$vin11}' or vdsplus='{$vin11ast}' or vdsplus='{$vin11ast_m1}' or vdsplus='{$vin11ast_m2}')) or
			(len=10 and (vdsplus='{$vin10}' or vdsplus='{$vin10ast}' or vdsplus='{$vin10ast_m1}' or vdsplus='{$vin10ast_m2}')) or
			(len=9 and (vdsplus='{$vin9}' or vdsplus='{$vin9ast}' or vdsplus='{$vin9ast_m1}' or vdsplus='{$vin9ast_m2}')) or
			(len=8 and (vdsplus='{$vin8}' or vdsplus='{$vin8ast}' or vdsplus='{$vin8ast_m1}' or vdsplus='{$vin8ast_m2}')) or
			(len=7 and (vdsplus='{$vin7}' or vdsplus='{$vin7ast}' or vdsplus='{$vin7ast_m1}' or vdsplus='{$vin7ast_m2}'))";
		$res = $this->db->query($qry);
		
		if($res->num_rows()>0){$modulefound = true;
			$W = $res->result_array()[0];

			$fidres["found"] = true;
			$fidres["module"] = $W['module'];
			$fidres["region"] = $W['region'];
			$fidres["vtype"] = $W['vtype'];
			$fidres["vdsplus"] = $W['vdsplus'];

			$areas[] = $fidres["region"];
		}
	}
}
?>