<?php
/**
## Version: 2.1 	-- Date: 05-SEP-2019 -- Jorge Jardim -- Added version information and revision history.
## Version: 2.1.1 	-- Date: 12-SEP-2019 -- Jorge Jardim -- Changed function getUserCodeFromId() to also check the passport number.
**/
class SableDBFunctions
{

	public function __construct(){}	
	public function __destruct(){}	
	
	public static function updateSessionVars()
	{
		global $sableDB;
		$_SESSION['ID']=session_id();
		$_SESSION['logged_in']=1;
		$ip=SableGeneralFunctions::getRealIpAddr(); //from procs.php
		$_SESSION['IP']=$ip;		
		
		$qry = "select user_code from member where session = '".session_id()."'";
		$uc = $sableDB->getValue($qry);
		
		$userQuery = "select user.*, access.* from user	inner join access on access.access_id = user.access_id
		where (user_code = ".$uc.") and (user.deleted = 0)";

		$_SESSION['user']  = $sableDB->getRow($userQuery);
		
		$_SESSION['user']['company_id'] = $uc ? SableDBFunctions::getCompanyCodeFromUserCode($_SESSION['user']['user_code']) : 0;
		$tst = $sableDB->recordCount;

		if (!isset($_SESSION['user']['user_code']))
		{
			SableSystem::logout('Session is corrupt. Please log in again.');
		}
		else
		{
			//$_SESSION['user']['user_title'] = self::getUserTitleFromCode($_SESSION['user']['user_code']);
			$_SESSION['user']['user_full_name'] = $_SESSION['user']['user_name'].' '.$_SESSION['user']['user_surname'];
			$_SESSION['company'] = $sableDB->getRow("select * from company where company_id = ".$_SESSION['user']['company_id']);
		}
	}
	
	public static function getCompanyName($coyCode)
	{
		global $sableDB;
		$coyName = $sableDB->getValue("select company_name from company where company_id = '".$coyCode."'");
		return $coyName;
	}
	
	public static function getCompanyType($coyCode)
	{
		global $sableDB;
		$coyType = $sableDB->getValue("select company_type from company where company_id = '".$coyCode."'");
		return $coyType;
	}
	
	public static function getUserEmailFromCode($userCode)
	{
		global $sableDB;
		return $sableDB->getValue("select user_email from user where user_code = ".$userCode);
	}
	
	public static function getUserTitleFromCode($userCode)
	{
		global $sableDB;
		$sql = 'select user_title 
		from user 
		inner join user_title on user_title.user_title_id = user.user_title_id 
		where user_code = '.$userCode;
		$title = $sableDB->getValue($sql);
		return $title;
	}
	
	public static function getUserDisplayNameFromUserCode($userCode)
	{
		global $sableDB;
		
		$sql = "select concat(user_name, ' ', user_surname) 
				from user 
				where user_code = '".$userCode."'";
		$userName = $sableDB->getValue($sql);
		$displayName = empty($userName)?'Sir/Madam':$userName;
		return $displayName;
	} /* function getUserDisplayName() */
	
	public static function getUserDisplayName($userEmail)
	{
		global $sableDB;
		$sql = "select concat(user_name, ' ', user_surname) as FULL_NAME 
				from user 
				where user_email = '".$userEmail."'";
		$userName = $sableDB->getValue($sql);
		$displayName = empty($userName)?'Sir/Madam':$userName;
		return $displayName;
	} /* function getUserDisplayName() */
	
	public static function getCompanyDisplayName($companyID)
	{
		global $sableDB;
		return $sableDB->getValue("select company_name from company where company_id = '".$companyID."'");
	} /* function getUserDisplayName() */
	
	public static function getUserCodeFromEmail($email)
	{
		global $sableDB;
		$query="select user_code from user where user_email = '".$email."'";
		return $sableDB->getValue($query);
	}
	
	public static function getUserCodeFromId($userId)
	{
		global $sableDB;
		$query="select user_code from user where user_id = '".$userId."' or user_passport = '".$userId."'";
		return $sableDB->getValue($query);
	}
	
	public static function getUserPhoneFromUserCode($userCode)
	{
		global $sableDB;
		return $sableDB->getValue('select coalesce(user_cell, user_phone) from user where user_code = '.$userCode);
	}
	
	public static function getCompanyCodeFromEmail($email)
	{
		global $sableDB;
		$query="select company_id from user where user_email = '".$email."'";
		return $sableDB->getValue($query);
	}
	
	public static function getCompanyCodeFromUserCode($userCode)
	{
		global $sableDB;
		$query="select company_id from user where user_code = ".$userCode;
		return $sableDB->getValue($query);
	}	
	
	public static function getUserEmailFromUserCode($userCode)
	{
		global $sableDB;
		return $sableDB->getValue('select user_email from user where user_code = '.$userCode);
	}
	
	public static function printOptions($table, $fieldValue, $fieldDescription, $default=0, $where="")
	{
		echo(self::getOptions($table, $fieldValue, $fieldDescription, $default, $where));
	}
	
	public static function getOptions($table, $fieldValue, $fieldDescription, $default=0, $where="")
	{
		global $sableDB;
		$cnt = 0;
		$moduleName = "";
		if (is_array($table)) //arrays allow for inner joins to get linked (foreign) fields
		{
			//first entry is always the main table.
			$sql = "select ".$fieldValue." as VAL, ".$fieldDescription." as DESCR ".PHP_EOL."from ".
					$table[0]['table_name']." ".PHP_EOL; 
			foreach($table as $itm)
			{
				if ($cnt)
				{
					$sql .= $itm['join']." join ".$itm['table_name']." on ".
					$itm['table_name'].".".$itm['field_name']." = ".
					$table[0]['table_name'].".".$itm['field_name'].PHP_EOL;
				}
				$cnt++;
			}
			$sql .= " ".$where.PHP_EOL." order by ".$fieldDescription;
			$moduleName = SableGeneralFunctions::getFormattedModuleName($table[0]['table_name']);
		}
		else 
		{
			$sql = "select ".$fieldValue." as VAL, ".$fieldDescription." as DESCR from ".$table." ".$where." order by ".$fieldDescription;
			$moduleName = SableGeneralFunctions::getFormattedModuleName($table);
		}

		//SableDebug::debug($sql);
		$rows = $sableDB->getRows($sql);
		
		$html = "";
		$sel = "";
		if ($default < 0) $html = "<option value=\"0\" selected=\"selected\">Select ".$moduleName."</option>";
		if (is_array($rows))
		{
			foreach($rows as $row)
			{
				if ($row['VAL']===$default) $sel = " selected='selected'"; else $sel = "";
				$html .= "<option value=\"".SableSystem::sableEncode($row['VAL'])."\"".$sel.">".$row['DESCR']."</option>";
			}
		} else $html = '<option value="0">No data</option>';
		return $html;
	}
	
	public static function printOptionsAdv($params)
	{
		echo(self::getOptionsAdv($params));
	}
	
	public static function getOptionsAdv($params)
	{
		/**
		PARAMS:
		$params['table_name'] 				--> Name of the table. (non array, i.e. no joins)
		$params['tables'] 					--> array of table names in the event of Joins being required. (Required)
			$params['tables'][x]['table_name'] 	--> Name of the table. $params['tables'][0] is always the main table.
			$params['tables'][x]['table_join_name'] 	--> Name of the table to join to (if not main table). $params['tables'][0] is always the main table.
			$params['tables'][x]['join_type'] 	--> 'inner', 'outer', 'left', etc.
			$params['tables'][x]['field_name'] 	--> field name to join on. Joining table and main table must have the same field names.
		$params['value_field_name'] 		--> Id field, etc. (Required)
		$params['caption_field_name'] 		--> Title/Short Description (Required)
		$params['selected_value'] 			--> Default selected value
		$params['order_field'] 				--> Order by
		$params['where_clause'] 			--> SQL where clause
		$params['description_field_name'] 	--> Long description
		$params['tag_name']					--> to add a tag to the element to store an additional value
		$params['tag_field_name']			--> the field name that holds the value required.
		$params['select_title']				--> Instead of "Select xxxx" we can have "Create New"
		$params['value_condition']			--> array to check for specific values of $params['value_field_name']
		$params['value_condition'][0]['field_name'] --> field to check [0] is the field name always
		$params['value_condition'][x]['value'] --> value to check for
		$params['value_condition'][x]['color'] --> set the background colour if the conditions are met
		$params['distinct'] --> true or false
		$params['group_field_name'] --> group by field name, comma separated if multiple values
		**/
		global $sableDB;
		$cnt = 0;
		$moduleName = "";
		if (is_array($params['tables'])) //arrays allow for inner joins to get linked (foreign) fields
		{
			//first entry is always the main table.
			$distinct = isset($params['distinct']) && ($params['distinct']);
			$valField = $distinct ? 'DISTINCT('.$params['value_field_name'].')' : $params['value_field_name'];
			$sql  = "select ".$valField." as VAL, ".$params['caption_field_name']." as CAPTION ";
			
			if (is_array($params['value_condition']) && !empty($params['value_condition']))
			{
				$sql .= ", ".$params['value_condition'][0]['field_name']." as COMP ";
			}
			
			if (isset($params['tag_field_name'])) $sql .= ", ".$params['tag_field_name']." as TAG ";
			if(isset($params['description_field_name'])&&(!empty($params['description_field_name'])))
			{
				$sql .= ", ".$params['description_field_name']." as DESCR ";
			}			
			$sql .= PHP_EOL."from ".$params['tables'][0]['table_name']." ".PHP_EOL; 
			foreach($params['tables'] as $itm)
			{
				if ($cnt)
				{
					$joinTable = isset($itm['table_join_name']) ? $itm['table_join_name'] : $params['tables'][0]['table_name'];
					$sql .= $itm['join_type']." join ".$itm['table_name']." on ".
					$itm['table_name'].".".$itm['field_name']." = ".$joinTable.".".$itm['field_name'].PHP_EOL;
				}
				$cnt++;
			}
			$sql .= " ".$params['where_clause'];
			$moduleName = SableGeneralFunctions::getFormattedModuleName($params['tables'][0]['table_name']);
		}
		else 
		{
			$sql  = "select ".$params['value_field_name']." as VAL, ".$params['caption_field_name']." as CAPTION ";
			if (isset($params['tag_field_name'])) $sql .= ", ".$params['tag_field_name']." as TAG ";
			if(isset($params['description_field_name'])&&(!empty($params['description_field_name'])))
			{
				$sql .= ", ".$params['description_field_name']." as DESCR ";
			}
			$sql .= PHP_EOL."from ".$params['table_name'].PHP_EOL.$params['where_clause'];
			$moduleName = SableGeneralFunctions::getFormattedModuleName($params['table_name']);
		}
		
		if (isset($params['group_field_name']))
		{
			$sql .= PHP_EOL." group by ".$params['group_field_name'];
		}

		$od = isset($params['order_desc']) ? " DESC " : "";
		if (isset($params['order_field']))
			$sql .= PHP_EOL." order by ".$params['order_field'].$od;
		else
			$sql .= PHP_EOL." order by ".$params['caption_field_name'].$od;
		//SableDebug::debug($sql);
		$rows = $sableDB->getRows($sql);
		
		$html = "";
		$sel = "";
		
		$default  = (isset($params['selected_value'])&&(!empty($params['selected_value'])))?$params['selected_value']:-1;
		$selTitle = (isset($params['select_title']) && !empty($params['select_title'])) ? $params['select_title'] : 'Select '.$moduleName;
		if ($default<0) $html = '<option value="0" selected="selected">'.$selTitle.'</option>';
		foreach($rows as $row)
		{
			$tag = isset($params['tag_name'])? $params['tag_name'].'="'.$row['TAG'].'"':'';
			$title = isset($row['DESCR'])?'title="'.$row['DESCR'].'"':'';
//			if ($row['DESCR']) $title = 'title="'.$row['DESCR'].'"'; else $title = 'title="'.$row['DESCR'].'"';
			if ($row['VAL']==$default) $sel = 'selected="selected"'; else $sel = '';
			
			if (is_array($params['value_condition']) && !empty($params['value_condition']))
			{
				foreach ($params['value_condition'] as $cond)
				{
					$style = '';
					if ($cond['value'] == $row['COMP'])
					{
						$style = ' style="background-color: '.$cond['color'].'" ';
						break;
					} 
				}
			}
			
			$html .= '<option value="'.SableSystem::sableEncode($row['VAL']).'"'.$sel.' '.$title.' '.$tag.' '.$style.'>'.$row['CAPTION'].'</option>';
		}
		return $html;
	}	
	
	/*
		getLinkedOptions is to like get options, but instead of specifying a field name for the description,
		you specify a table name, field id name and field desc name with an id to get a description from a separate table.
		We use an array for simplicity
	*/
	public static function getLinkedOptions($param)
	{
		//$param['read_table']
		//$param['value_field']
		//$param['desc_field']
		//$param['link_field']
		//$param['link_table']
		//$param['where']
		//$param['default']
		
		global $sableDB;
		
		$sql = "select ".$param['value_field']." as VAL, ".$param['desc_field']." as DESCR from ".$param['read_table'].
		" inner join ".$param['link_table']." on ".$param['link_table'].".".$param['link_field']." = ".$param['read_table'].
		".".$param['link_field']." ".$param['where'];
		
		$result = $sableDB->getRows($sql);
		$cnt = $sableDB->recordCount;
		$html = "";
		$sel = "";
		if ($default<0) 
			$html = "<option value=\"0\" selected=\"selected\">Select ".
			SableGeneralFunctions::getFormattedModuleName($param['read_table'])."</option>";
		for($i=0; $i < $cnt; $i++)
		{
			$row = $result[$i];
			if ($row['VAL']===$default) $sel = " selected='selected'"; else $sel = "";
			$html .= "<option value=\"".SableSystem::sableEncode($row['VAL'])."\"".$sel.">".$row['DESCR']."</option>";
		}
		return $html;
	}
	
	public static function loadAuditDetail($userCode, $desc, $recordId)
	{
		global $sableDB;
		$sql = "insert into audit (audit_desc, item_id, ip_address, user_code, audit_code) values ('".
				$desc."', ".$recordId.", '".SableGeneralFunctions::getRealIpAddr()."', ".$userCode.", 0)";
		$res = $sableDB->updateQuery($sql);
	}
	
	public static function useVAT()
	{
		//sable_vat_registered
		global $sableDB;
		$dt = empty($date)?date('Y-m-d'):$date;
		$sql = 'select sable_vat_registered from sable_settings 
				where (sable_settings_from <= "'.$dt.'") and ((sable_settings_to = "0000-00-00") or (sable_settings_to >= "'.$dt.'"))';
		return $sableDB->getValue($sql);		
	}
	
	public static function getVATValue($date="")
	{
		global $sableDB;
		$dt = empty($date)?date('Y-m-d'):$date;
		$sql = 'select sable_settings_vat from sable_settings 
				where (sable_settings_from <= "'.$dt.'") and ((sable_settings_to = "0000-00-00") or (sable_settings_to >= "'.$dt.'"))';
		return $sableDB->getValue($sql);
	}
	
	public static function getMarkupValue($date="")
	{
		global $sableDB;
		$dt = empty($date)?date('Y-m-d'):$date;
		$sql = 'select sable_markup_percent from sable_settings 
				where (sable_settings_from <= "'.$dt.'") and ((sable_settings_to = "0000-00-00") or (sable_settings_to >= "'.$dt.'"))';
		return $sableDB->getValue($sql);
	}	
	
	//SableDBFunctions::storeArray(('user__notification','user_code',$this->hddnUserCode,'notification_id', $items))
	public static function storeArray($tableName, $idFieldName, $idValue, $foreignFieldName, $valuesArray)
	{
		global $sableDB;
		$cnt = 0;
		$cntSQL = 'select count(*) from '.$tableName.' where '.$idFieldName.' = '.$idValue;
		$cnt = $sableDB->getValue($cntSQL);
		$delSQL = 'delete from '.$tableName.' where '.$idFieldName.' = '.$idValue;
		$res = ($cnt)?$sableDB->updateQuery($delSQL):1;
		if ($res && (!empty($valuesArray)))
		{
			$res = 0; //reset
			foreach($valuesArray as $itm)
			{
				if (isset($itm))
				{
					$sql = 'insert into '.$tableName.' ('.$idFieldName.', '.$foreignFieldName.') 
							values ('.$idValue.', "'.SableSystem::sableDecode($itm['value']).'");';
					$res = $sableDB->updateQuery($sql);
					if ($res) $cnt++;
				}
			}
			return $res;
		} else return 1;
	}
	
	public static function getCountryDataFromCountryId($countryId)
	{
		global $sableDB;
		return $sableDB->getRow("select * from country where country_id = ".$countryId);
	}
	
	public static function getCountryDataFromAddressId($addressId)
	{
		global $sableDB;
		$countrySQL = 	"select country.country_id, country_nicename, country_phonecode from address
						inner join country on country.country_id = address.country_id
						where address_id = ".$addressId;
		return $sableDB->getRow($countrySQL);
	}
	
	public static function reverseGeocodeAddress($lat, $lng)
	{
		// set your API key here
		// format this string with the appropriate latitude longitude
		$addr = 'Unknown';
		$url = 'http://maps.google.com/maps/geo?q='.$lat.','.$lng.'&output=json&sensor=true_or_false&key='.GOOGLE_API_KEY;

		// make the HTTP request
		$data = @file_get_contents($url);
		// parse the json response
		$jsondata = json_decode($data,true);
		// if we get a placemark array and the status was good, get the addres

		if (is_array($jsondata) && $jsondata['Status']['code']==200)
		{
			  $addr = $jsondata ['Placemark'][0]['address'];
		}
                      	SableDebug::debug('address ID not found '+$addr, 1);

		return $addr;
	}
	
	public static function getAddressIdFromCoords($lat, $lng)
	{
		global $sableDB;
		$sql = "select address_id 
				from address 
				where 
				(address_lat = '{$lat}') and 
				(address_lng = '{$lng}') and 
				(address_lat <> '') and 
				(address_lng <> '')";
		return $sableDB->getValue($sql);
	}
	
	public static function getAddressIdFromAddress($address)
	{
		global $sableDB;
		$sql = "select address_id from address where (address_street = '{$address}')";
		return $sableDB->getValue($sql);
	}
	
	public static function getAddressId($address='', $lat='', $lng='')
	{
		if (empty($address) && (empty($lat) || empty($lng)))
		{
			throw new Exception('Not enough parameter values given.');		
		}
		else
		{
			$addressId = 0;
			if (!empty($address) && !empty($lat))
			{
				$addressId = self::getAddressIdFromCoords($lat, $lng);
				if (!$addressId) $addressId = self::getAddressIdFromAddress($address);
				return intval($addressId);
			}
			else
			{
				if (empty($address))
				{
					$addressId = self::getAddressIdFromCoords($lat, $lng);
				}
				else
				{
					$addressId = self::getAddressIdFromAddress($address);
				}
				return intval($addressId);
			}
		}
	}
	
	private static function saveAddressToDB($address, $lat, $lng, $type)
	{
		global $sableDB;
		
		//find a postal code:
		$postid = 0;
		$qry = "";
		$arr = explode(',', $address);
		$found = false;
		$reg	= "";
		$sub	= "";
		$whr	= "";
		$orc	= 0;
		$or		= "";
		$cnt	= 0;

		foreach ($arr as $pc)
		{
			$or = $orc ? ' or ' : '';
			$sb = trim($pc);
			if (!empty($sb)) $reg .= $or.'(region_name = "'.$sb.'")';
			if (!empty($sb)) $sub .= $or.'(suburb_name = "'.$sb.'")';
			if (is_numeric($pc))
			{

				$cd = sprintf('%04d', $pc);
				$qry = 'select post_id 
						from postal_data 
						where ((str_code = "'.$cd.'") or (box_code = "'.$cd.'")) ';
				$cnt++;
			}
			$orc++;
		}

		if ($cnt)
		{
			$whr .= "and (".$reg.") or (".$sub.")";
			$qry .= $whr;

			$postid = $sableDB->getValue($qry);
			
			if ($postid)
			{
				$addressId = self::getAddressId($address, $lat, $lng);
				if (!$addressId)
				{
					$sql = "insert into address 
							(address_type_id, country_id, post_id, address_Street, address_lng, address_lat) 
							values 
							(".$type.", 197, ".$postid.", '".$address."', '".$lng."', '".$lat."')";

					$res = $sableDB->updateQuery($sql);
					$addressId = self::getAddressIdFromCoords($lat, $lng);
				}
				return intval($addressId);

			}
			else
			{
				SableSystem::ajaxDisplayMessage('Could not find postal data to match this address.');
				return 0;
			}
		}
		else
		{
			//try to find a postal code for this address:
			$found = false;
			$reg	= "";
			$sub	= "";
			$whr	= "";
			$orc	= 0;
			$or		= "";
			$cnt	= 0;
		
			$qry = 'select post_id, coalesce(str_code, box_code) as postal_code
					from postal_data 
					where ';
			foreach ($arr as $pc)
			{
				$or = $orc ? ' or ' : '';
				$sb = trim($pc);
				if (!empty($sb)) $reg .= $or.'(region_name = "'.$sb.'")';
				if (!empty($sb)) $sub .= $or.'(suburb_name = "'.$sb.'")';
				$orc++;
				$qry .= $or.$reg.' or '.$sub;
			}
			$qry .= ' limit 0,1';		
			
			$postrow = $sableDB->getRow($qry);
			
			if ($postrow['post_id'])
			{
				$address .= ', '.$postrow['postal_code']; //add the correct postal code which seems to be missing.
				
				$addressId = self::getAddressId($address, $lat, $lng);
				if (!$addressId)
				{
					$sql = "insert into address 
							(address_type_id, country_id, post_id, address, address_lng, address_lat) 
							values 
							(".$type.", 197, ".$postid.", '".$address."', '".$lng."', '".$lat."')";

					$res = $sableDB->updateQuery($sql);
					$addressId = self::getAddressIdFromCoords($lat, $lng);
				}
				return intval($addressId);
			}
			else
			{
				SableSystem::ajaxDisplayMessage('This address is missing a postal code.');
				return 0;
			}
		}	
	}
	
	public static function storeAddress($address, $lat, $lng, $type = 1)
	{
		/**
			Saves the address and returns the newly created address id.
			Address type 1 = Physical Address (default).
			
			We will first check if the address doesn't already exist, then return that address,
			otherwise we add the address and return the new id:			
		**/
		global $sableDB;
		
		$address = trim($address);
		if (!empty($address) && !empty($lng) && !empty($lat))
		{
			//addresses could have the same Co-ords, but the address could be different - think of a duet, or block of flats:
            
                            $addressStreet = substr($addr['tag'], 0, strpos($addr['tag'], ','));
                            $addressSuburb = substr($addr['tag'], strlen($addressStreet) + 1, strpos($addr['tag'], ','));
                            $addressCity = substr($addr['tag'], strlen($$addressSuburb) + 1, strpos($addr['tag'], ','));
                            $addressCounty = substr($addr['tag'], strlen($addressCity) + 1, strpos($addr['tag'], ','));
                            $addressPostalCode = substr($addr['tag'], strlen($addressCounty) + 1, strpos($addr['tag'], ','));

              	SableDebug::debug('address check '+$address['tag'], 1);

                
				$land = empty($this->selCountryCode) ? 197 : 0;
				$sql = 'insert ignore into address (address_type_id, country_id, address_street, address_suburb, address_city, address_county, address_postal_code) 
						values ('.$addr['value'].', '.$land.', "'.$addressStreet.'", "'.$$addressSuburb.'", "'.$addressCity.'", "'.$addressCounty.'", "'.$addressPostalCode.'")';
				$sableDB->updateQuery($sql);
				$addressIds[]['value'] = $sableDB->getValue('select address_id from address	where address_street = "'.$address.'" and address_type_id = '.$addr['value'].' and country_id = '.$land);
            
			$sql		= 'select address_id 
							from address 
							where address_street = "'.$address.'" and 
							address_type_id = '.$type.' and 
							address_lng = "'.$lng.'" and 
							address_lat = "'.$lat.'"';
            
			$addressId	= intval($sableDB->getValue($sql));
			
			if (!$addressId)
			{
                
              	SableDebug::debug('address ID not found '+$address['tag'], 1);

				$addressId = self::saveAddressToDB($address, $lat, $lng, $type);
			}
			
			return $addressId;
		}
		else
		{
			if ((!empty($address)) || (!empty($lng) && !empty($lat)))
			{
                                    SableDebug::debug('address reverse geocoding checks ');
                $testLng = strval($lng);
                                        SableDebug::debug($testLng, 1);               
                $testLat = strval($lat);
                                        SableDebug::debug($testLat, 1);
                

                //mcp
				//$addressId = intval(self::getAddressIdFromCoords($lat, $lng));
                
                                    $testVara = strval($addressId);
                                        SableDebug::debug($testVara, 1);
                
				if (!$addressId)
				{
					if (empty($address))
					{
                        
                     SableDebug::debug('address reverseGeocodeAddress ' + $address);

						$addressStreet = self::reverseGeocodeAddress($lat, $lng);
					}
                    


					$sql		= 'select address_id from address where address_street = "'.$address.'" and address_type_id = '.$type.'';
					$addressId	= intval($sableDB->getValue($sql));
				}

				if (!$addressId)
				{
                    SableDebug::debug('address ID save AddressToDB ');
					self::saveAddressToDB($address, $lat, $lng, $type);
				}
				else
				{
                    $testVar = strval($addressId);
                                        SableDebug::debug($testVar, 1);
                    

                    $sql		= 'select address_street from address where address_id = '.$addressId.'';
                    
					$addressRow = $sableDB->getRow($sql);
					//ensure we accept changes to the address also:

					if ($address !== $addressRow['address_street'])
					{
                        SableDebug::debug('address ID accept changes', 1);

						$sql	= 'select address_id from address where address_street = "'.$address.'" and 
									address_type_id = '.$type.' and address_id <> '.$addressId.'';
						$adid2	= intval($sableDB->getRow($sql));
						if ($adid2)
							$addressId = $adid2;
						else
						{
							$sql	= 'update address set address_street = "'.$address.'" where address_id = '.$addressId.'';
							$res	= $sableDB->updateQuery($sql);
						}
					}
				}			
				return $addressId;
			} else return 0;
		}
	}
	
	public static function storeLookupValue($tableName, $fieldId, $fieldName, $value)
	{
		global $sableDB;
		if (!empty($value))
		{
			$id = $sableDB->getValue('select '.$fieldId.' from '.$tableName.' where '.$fieldName.' = "'.$value.'"');
			if (!$id)
			{
				$sql = 'insert into '.$tableName.' ('.$fieldName.') values ("'.$value.'")';
				$res = $sableDB->updateQuery($sql);
				if ($res) $id = $sableDB->getValue('select '.$fieldId.' from '.$tableName.' where '.$fieldName.' = "'.$value."'");
			} return $id;
		} else return 0;		
	}
	
	public static function getAccessIdFromPositionId($positionId)
	{
		global $sableDB;
		return $sableDB->getValue('select access_id from position where position_id = '.$positionId);
	}
	
	public static function storeAjaxResponse($paramArray)
	{
		global $sableDB;
		//will return the hash used to reference this record;
		$hash = SableSystem:: generateHash(9);
		$params = base64_encode(json_encode($paramArray));
		$params = str_replace('+', '|', $params);		
		$sql = 'insert into ajax_response (ajax_hash, ajax_params) values ("'.$hash.'", "'.$params.'")';
		$res = $sableDB->updateQuery($sql);
		if ($res) return $hash; else return 0;
	}
	
	public static function deleteAjaxResponse($hash)
	{
		global $sableDB;
		$sql = 'delete from ajax_response where ajax_hash = "'.$hash.'"';
		$res = $sableDB->updateQuery($sql);
		return $res;
	}
	
	public static function getAjaxResponse($hash, $delete = true)
	{
		global $sableDB;
		$sql = 'select ajax_params from ajax_response where ajax_hash = "'.$hash.'"';
		$data = $sableDB->getValue($sql);
		$params = str_replace('|', '+', $data);
		$out = json_decode(base64_decode($params), true);
		
		if ($delete) $res = self::deleteAjaxResponse($hash);
		
		return $out;
	}
	
	public static function clearListQueryFilter($tableName, $userCode = 0)
	{
		global $sableDB;
		$uc = intval($userCode) ? $userCode : $_SESSION['user']['user_code'];
		$sql = "delete from list_query 
				where user_code = {$uc} 
				and list_query_module_folder = '{$tableName}'";
		return $sableDB->updateQuery($sql); //clear any refined search filters
	}
	
	public static function getDistanceMatrixBetweenAddresses($origAddressId, $destAddressId)
	{
		global $sableDB;
		$sql = "select distance, duration, distance_text, duration_text 
								from address_distance 
								where (address_id_from = {$origAddressId} and address_id_to = {$destAddressId}) or 
								(address_id_to = {$origAddressId} and address_id_from = {$destAddressId})";
		$row = $sableDB->getRow($sql);
		return (empty($row) ? 0 : $row);
	}
	
	public static function storeDistanceMatrix($origAddressId, $destAddressId, $distance, $duration)
	{
		global $sableDB;
		$qry = "insert into address_distance (address_id_from, address_id_to, distance, duration, distance_text, duration_text) values 
		({$origAddressId}, {$destAddressId}, {$distance['value']}, {$duration['value']}, 
		'{$distance['text']}', '{$duration['text']}') 
		on duplicate key update 
		distance = {$distance['value']}, 
		duration = {$duration['value']},
		distance_text = '{$distance['text']}', 
		duration_text = '{$duration['text']}'";

		return $sableDB->updateQuery($qry);
	}
}