Replace address lookup with access to 'address' package generic database.

This adds map location data to the address and links to OSM, multimap and googlemap

3 files changed, 50 insertions, 30 deletions

diff --git a/Contact.php b/Contact.php
index e6a658c..c17c2d4 100644
--- a/Contact.php
+++ b/Contact.php
@@ -70,16 +70,18 @@ class Contact extends LibertyContent {
 	function load($pContentId = NULL) {
 		if ( $pContentId ) $this->mContentId = (int)$pContentId;
 		if( $this->verifyId( $this->mContentId ) ) {
- 			$query = "select con.*, lc.*, ca.*,
-				x00.`xkey_ext` as name, lc.`title` as organisation, 
+ 			$query = "select con.*, lc.*,
+ 				ap.*, xhC.`xkey_ext` AS house,
+ 				x00.`xkey_ext` as name, lc.`title` as organisation, 
 				uue.`login` AS modifier_user, uue.`real_name` AS modifier_real_name,
 				uuc.`login` AS creator_user, uuc.`real_name` AS creator_real_name
 				FROM `".BIT_DB_PREFIX."contact` con
 				LEFT JOIN `".BIT_DB_PREFIX."liberty_content` lc ON lc.content_id = con.content_id
 				LEFT JOIN `".BIT_DB_PREFIX."users_users` uue ON (uue.`user_id` = lc.`modifier_user_id`)
 				LEFT JOIN `".BIT_DB_PREFIX."users_users` uuc ON (uuc.`user_id` = lc.`user_id`)
-				LEFT JOIN `".BIT_DB_PREFIX."contact_address` ca ON ca.content_id = con.content_id
-				LEFT JOIN `".BIT_DB_PREFIX."contact_xref` x00 ON x00.content_id = con.content_id AND x00.source = '$00'
+				LEFT JOIN `".BIT_DB_PREFIX."contact_xref` x00 ON x00.`content_id` = con.`content_id` AND x00.`source` = '$00'
+				LEFT JOIN `".BIT_DB_PREFIX."contact_xref` xhC ON xhC.`content_id` = con.`content_id` AND xhC.`source` = '#C' AND ( xhC.`end_date` IS NULL OR xhC.`end_date` > CURRENT_TIMESTAMP )
+				LEFT JOIN `".BIT_DB_PREFIX."address_postcode` ap ON ap.`postcode` = xhC.`xkey`
 				WHERE con.`content_id`=?";
 			$result = $this->mDb->query( $query, array( $this->mContentId ) );
 //				LEFT JOIN `".BIT_DB_PREFIX."contact` ci ON ci.contact_id = pro.owner_id
@@ -105,11 +107,14 @@ class Contact extends LibertyContent {
 				$this->mInfo['name'] = trim($this->mInfo['name']).' '.$this->mInfo['surname'];
 				$this->mInfo['name'] = trim($this->mInfo['name']).' '.$this->mInfo['suffix'];
 				$this->mInfo['name'] = trim($this->mInfo['name']);
-//				$os1 = new OSRef($this->mInfo['x_coordinate'], $this->mInfo['y_coordinate']);
-//				$ll1 = $os1->toLatLng();
-//				$this->mInfo['prop_lat'] = $ll1->lat;
-//				$this->mInfo['prop_lng'] = $ll1->lng;
 
+				if ( $this->mInfo['postcode'] and $this->mInfo['grideast'] <> '00000' ) {
+					$os1 = new OSRef( $this->mInfo['grideast']*10, $this->mInfo['gridnorth']*10 );
+					$ll1 = $os1->toLatLng();
+					$this->mInfo['x_coordinate'] = $ll1->lat;
+					$this->mInfo['y_coordinate'] = $ll1->lng;
+				}
+				
 				$this->loadContentTypeList();
 				if ( $this->mInfo['contact_types'][2]['content_id'] ) { 
 					$this->loadClientList();
@@ -396,26 +401,28 @@ class Contact extends LibertyContent {
 		}
 */
 		if( isset( $find_location ) and is_string( $find_location ) and $find_location <> '' ) {
-			$whereSql .= " AND ( UPPER( ca.`street` ) like ? OR UPPER( ca.`locality` ) like ? OR UPPER( ca.`town` ) like ? )";
+			$whereSql .= " AND ( UPPER( ap.`add2` ) like ? OR UPPER( ap.`add3` ) like ? OR UPPER( ap.`add4` ) like ? OR UPPER( ap.`town` ) like ? )";
 			$uploc = '%' . strtoupper( $find_location ). '%';;
 			$bindVars[] = $uploc;
 			$bindVars[] = $uploc;
 			$bindVars[] = $uploc;
+			$bindVars[] = $uploc;
 			$pParamHash["listInfo"]["ihash"]["find_location"] = $find_location;
 		}
 
 		if( isset( $find_postcode ) and is_string( $find_postcode ) and $find_postcode <> '' ) {
-			$whereSql .= " AND UPPER( `ca.postcode` ) LIKE ? ";
+			$whereSql .= " AND UPPER( `ap.postcode` ) LIKE ? ";
 			$bindVars[] = '%' . strtoupper( $find_postcode ). '%';
 			$pParamHash["listInfo"]["ihash"]["find_postcode"] = $find_postcode;
 		}
 
-		$query = "SELECT con.`content_id` as content_id, con.*, lc.*, ca.*,
-			(SELECT COUNT(*) FROM `".BIT_DB_PREFIX."contact_xref` x WHERE x.`content_id` = con.`content_id` AND x.`source` NOT STARTING WITH '$' ) AS refs,
-			(SELECT COUNT(*) FROM `".BIT_DB_PREFIX."contact_address` a WHERE a.`content_id` = con.`content_id` ) AS `addresses`
+		$query = "SELECT con.`content_id` as content_id, con.*, lc.*,
+			ap.*, xhC.`xkey_ext` AS house,
+			(SELECT COUNT(*) FROM `".BIT_DB_PREFIX."contact_xref` x WHERE x.`content_id` = con.`content_id` AND x.`source` NOT STARTING WITH '$' ) AS refs
 			FROM `".BIT_DB_PREFIX."contact` con
 			LEFT JOIN `".BIT_DB_PREFIX."liberty_content` lc ON lc.`content_id` = con.`content_id`
-			LEFT JOIN `".BIT_DB_PREFIX."contact_address` ca ON ca.`content_id` = con.`content_id`
+			LEFT JOIN `".BIT_DB_PREFIX."contact_xref` xhC ON xhC.`content_id` = con.`content_id` AND xhC.`source` = '#C' AND ( xhC.`end_date` IS NULL OR xhC.`end_date` > CURRENT_TIMESTAMP )
+			LEFT JOIN `".BIT_DB_PREFIX."address_postcode` ap ON ap.`postcode` = xhC.`xkey`
 			$findSql
 			$joinSql 
 			WHERE lc.`content_type_guid` = ? $whereSql
@@ -425,7 +432,8 @@ class Contact extends LibertyContent {
 		$query_cant = "SELECT COUNT( * )
 			FROM `".BIT_DB_PREFIX."contact` con
 			LEFT JOIN `".BIT_DB_PREFIX."liberty_content` lc ON lc.content_id = con.content_id
-			LEFT JOIN `".BIT_DB_PREFIX."contact_address` ca ON ca.content_id = con.content_id
+			LEFT JOIN `".BIT_DB_PREFIX."contact_xref` xhC ON xhC.`content_id` = con.`content_id` AND xhC.`source` = '#C' AND ( xhC.`end_date` IS NULL OR xhC.`end_date` > CURRENT_TIMESTAMP )
+			LEFT JOIN `".BIT_DB_PREFIX."address_postcode` ap ON ap.`postcode` = xhC.`xkey`
 			$joinSql WHERE lc.`content_type_guid` = ? $whereSql ";
 		$result = $this->mDb->query( $query, $bindVars, $max_records, $offset );
 		$ret = array();
diff --git a/templates/display_address.tpl b/templates/display_address.tpl
index a2a7620..9235eb1 100644
--- a/templates/display_address.tpl
+++ b/templates/display_address.tpl
@@ -7,10 +7,16 @@
 					{$pageInfo.pao},<br />{/if}
 				{if isset($pageInfo.number) && ($pageInfo.number <> '') }
 					{$pageInfo.number},<br />{/if}
-				{if isset($pageInfo.street) && ($pageInfo.street <> '') }
-					{$pageInfo.street},<br />{/if}
-				{if isset($pageInfo.locality) && ($pageInfo.locality <> '') }
-					{$pageInfo.locality},&nbsp;{/if}
+				{if isset($pageInfo.house) && ($pageInfo.house <> '') }
+					{$pageInfo.house},<br />{/if}
+				{if isset($pageInfo.add1) && ($pageInfo.add1 <> '') }
+					{$pageInfo.add1},<br />{/if}
+				{if isset($pageInfo.add2) && ($pageInfo.add2 <> '') }
+					{$pageInfo.add2},&nbsp;{/if}
+				{if isset($pageInfo.add3) && ($pageInfo.add3 <> '') }
+					{$pageInfo.add3},<br />{/if}
+				{if isset($pageInfo.add4) && ($pageInfo.add4 <> '') }
+					{$pageInfo.add4},&nbsp;{/if}
 				{if isset($pageInfo.town) && ($pageInfo.town <> '') }
 					{$pageInfo.town},&nbsp;{/if}
 				{if isset($pageInfo.county) && ($pageInfo.county <> '') }
@@ -23,8 +29,12 @@
 			{formlabel label="Visual Centre Coordinates" for="street_start_x"}
 			{forminput}
 				Easting: {$pageInfo.x_coordinate|escape} Northing: {$pageInfo.y_coordinate|escape}
-				&nbsp;&lt;<a href="http://www.multimap.com/maps/?map={$pageInfo.prop_lat},{$pageInfo.prop_lng}|17|4&loc=GB:{$pageInfo.prop_lat}:{$pageInfo.prop_lng}:17" title="{$pageInfo.title}">
+				&nbsp;&lt;<a href="http://www.openstreetmap.org/index.html?mlat={$pageInfo.x_coordinate}&mlon={$pageInfo.y_coordinate}&zoom=16&layers=BOFT" title="{$propertyInfo.title}">
+					OpenStreetMap
+				</a>&gt;&nbsp;&lt;<a href="http://www.bing.com/maps/?cp={$pageInfo.x_coordinate}~{$pageInfo.y_coordinate}&lvl=16&style=r#" title="{$pageInfo.title}">
 					Multimap
+				</a>&gt;&nbsp;&lt;<a href="http://www.google.co.uk/maps?f=q&source=s_q&hl=en&ll={$pageInfo.x_coordinate},{$pageInfo.y_coordinate}&z=16" title="{$pageInfo.title}">
+					Google
 				</a>&gt;<br />
 				{$pageInfo.rpa|escape}
 			{/forminput}
diff --git a/templates/list.tpl b/templates/list.tpl
index f6f199a..b882623 100644
--- a/templates/list.tpl
+++ b/templates/list.tpl
@@ -41,21 +41,23 @@
 						{if isset($listcontacts[content].uprn) && ($listcontacts[content].uprn <> '') }UPRN: {$listcontacts[content].uprn}&nbsp;&nbsp;{/if}
 						{if isset($listcontacts[content].postcode) && ($listcontacts[content].postcode <> '') }
 							{tr}Address{/tr}&nbsp;-&nbsp; 
-							{if isset($listcontacts[content].sao) && ($listcontacts[content].sao <> '') }
-								{$listcontacts[content].sao},&nbsp;{/if}
-							{if isset($listcontacts[content].pao) && ($listcontacts[content].pao <> '') }
-								{$listcontacts[content].pao},&nbsp;{/if}
-							{if isset($listcontacts[content].number) && ($listcontacts[content].number <> '') }
-								{$listcontacts[content].number},&nbsp;{/if}
-							{if isset($listcontacts[content].street) && ($listcontacts[content].street <> '') }
-								{$listcontacts[content].street},&nbsp;{/if}
-							{if isset($listcontacts[content].locality) && ($listcontacts[content].locality <> '') }
-								{$listcontacts[content].locality},&nbsp;{/if}
+							{if isset($listcontacts[content].house) && ($listcontacts[content].house <> '') }
+								{$listcontacts[content].house},&nbsp;{/if}
+							{if isset($listcontacts[content].add1) && ($listcontacts[content].add1 <> '') }
+								{$listcontacts[content].add1},&nbsp;{/if}
+							{if isset($listcontacts[content].add2) && ($listcontacts[content].add2 <> '') }
+								{$listcontacts[content].add2},&nbsp;{/if}
+							{if isset($listcontacts[content].add3) && ($listcontacts[content].add3 <> '') }
+								{$listcontacts[content].add3},&nbsp;{/if}
+							{if isset($listcontacts[content].add4) && ($listcontacts[content].add4 <> '') }
+								{$listcontacts[content].add4},&nbsp;{/if}
 							{if isset($listcontacts[content].town) && ($listcontacts[content].town <> '') }
 								{$listcontacts[content].town},&nbsp;{/if}
 							{if isset($listcontacts[content].county) && ($listcontacts[content].county <> '') }
 								{$listcontacts[content].county},&nbsp;{/if}
 							{$listcontacts[content].postcode}&nbsp;&nbsp;
+							{if isset($listcontacts[content].grideast) }
+								&lt;{$listcontacts[content].grideast|escape}&nbsp;,&nbsp;{$listcontacts[content].gridnorth|escape}&gt;&nbsp;{/if}
 						{/if}
 						<br />
 						{tr}Refs{/tr}: {$listcontacts[content].refs|default:0}&nbsp;&nbsp;