<?php
include('/home/jonacspz/champ.jona.co.za/includes/sysinit.php');
require(SABLE_CLASS_PATH.'sable_import.php');

/*
truncate table postal_data;
truncate table region;
truncate table suburb;
*/

function getProvinceId($postcode)
{
	$boxcode = is_numeric($postcode) ? intval($postcode) : 0;
	$provinceId = 0;

	if (($boxcode >= 1) && ($boxcode <= 299)){$provinceId = 4;}
	if (($boxcode >= 300) && ($boxcode <= 499)){$provinceId = 9;}
	if (($boxcode >= 500) && ($boxcode <= 999)){$provinceId = 6;}
	if (($boxcode >= 1000) && ($boxcode <= 1399)){$provinceId = 7;}
	if (($boxcode >= 1400) && ($boxcode <= 2199)){$provinceId = 4;}
	if (($boxcode >= 2200) && ($boxcode <= 2499)){$provinceId = 7;}
	if (($boxcode >= 2500) && ($boxcode <= 2899)){$provinceId = 9;}
	if (($boxcode >= 2900) && ($boxcode <= 4730)){$provinceId = 5;}
	if (($boxcode >= 4731) && ($boxcode <= 6499)){$provinceId = 2;}
	if (($boxcode >= 6500) && ($boxcode <= 8099)){$provinceId = 1;}
	if (($boxcode >= 8100) && ($boxcode <= 8999)){$provinceId = 8;}
	if (($boxcode >= 9300) && ($boxcode <= 9999)){$provinceId = 3;}

	return $provinceId;
}


function getRegionId($provinceId, $regionName)
{
	global $sableDB;
	$sql = 'select region_id from region where (province_id = '.$provinceId.') and (region_name = "'.$regionName.'")';
	$regionId = $sableDB->getValue($sql);
	return $regionId;
}

$sableDB->updateQuery('truncate table postal_data');
$sableDB->updateQuery('truncate table region');
$sableDB->updateQuery('truncate table suburb');

$sableImport = new SableImport;
$sableImport->fileName = '/home/jonacspz/champ.jona.co.za/lib/postalcodes.csv';
$sableImport->hasHeader = true;
$sableImport->tableName = 'postal_data';
$sableImport->delimeter = ',';
$sableImport->neatenValues = true;
$sableImport->fieldNames = ['suburb_name', 'box_code', 'str_code', 'region_name'];
//suburb, box code, street code, area name
$sableImport->ImportFile();
$err = $sableImport->error;
$msg = $sableImport->errorMessage;
unset($sableImport);
if ($err) echo($msg.PHP_EOL);
echo('Busy...'.PHP_EOL);

//now we load the data into the relevant tables as needed:
$rows = $sableDB->getRows('select * from postal_data');
//SableDebug::debug($rows);
$count = 0;
foreach($rows as $row)
{
	$provinceId = getProvinceId($row['str_code']);
	if (!$provinceId) $provinceId = getProvinceId($row['box_code']);
	$suburbName = $row['suburb_name'];
	$boxCode 	= sprintf('%04d', intval($row['box_code']));
	$streetCode = sprintf('%04d', intval($row['str_code']));
	$regionName = $row['region_name'];

	$regionId = getRegionId($provinceId, $regionName);
	
	if (!$regionId)
	{	
		$sql = 'insert ignore into region (province_id, region_name) values 
		 ('.$provinceId.', "'.$regionName.'")';
		$sableDB->updateQuery($sql);		
	
		$regionId = getRegionId($provinceId, $boxCode, $streetCode);
	}
	
	$sql = 'insert ignore into suburb (region_id, suburb_name) values ('.$regionId.', "'.$suburbName.'")';
	$sableDB->updateQuery($sql);
	$count++;
}
echo(PHP_EOL.$count.' records added.');