-- Function: at_ls_buildfrom_addresspremium()

-- DROP FUNCTION at_ls_buildfrom_addresspremium();

CREATE OR REPLACE FUNCTION at_ls_buildfrom_addresspremium()
  RETURNS void AS
$BODY$

BEGIN

-- Drop the temporary table
PERFORM at_sys_drop_table('tmpTableBuild');
CREATE TABLE "tmpTableBuild"
(
  "UPRN" text,
  "USRN" text,
  "EASTING" text,
  "NORTHING" text,
  "POSTCODE" text,
  "SAON_START_NUM" text,
  "SAON_START_SUFFIX" text,
  "SAON_END_NUM" text,
  "SAON_END_SUFFIX" text,
  "SAON_TEXT" text,
  "PAON_START_NUM" text,
  "PAON_START_SUFFIX" text,
  "PAON_END_NUM" text,
  "PAON_END_SUFFIX" text,
  "PAON_TEXT" text,
  "DESCRIPTION" text,
  "POSTTOWN" text,
  "TOWN" text,
  "LOC" text,
  "DISTRICT" text,
  "ORGANISATION" text,
  "COUNTY" text,
  "PostalAddress" character varying,
  "Suffix" character varying
)

WITH (
  OIDS=FALSE
);

INSERT INTO "tmpTableBuild" SELECT 
	b.uprn as "UPRN",
	s.usrn as "USRN", 
	b.easting as "EASTING",
	b.northing as "NORTHING",
	coalesce(b.postcodelocator) AS "POSTCODE",
	coalesce(l.saostartnumber) as "SAON_START_NUM", 
	coalesce(l.saostartsuffix) as "SAON_START_SUFFIX", 
	coalesce(l.saoendnumber) as "SAON_END_NUM", 
	coalesce(l.saoendsuffix) AS "SAON_END_SUFFIX", 
	coalesce(initcap(l.saotext)) AS "SAON_TEXT", 
	coalesce(l.paostartnumber) AS "PAON_START_NUM", 
	coalesce(l.paostartsuffix) AS "PAON_START_SUFFIX", 
	coalesce(l.paoendnumber) AS "PAON_END_NUM", 
	coalesce(l.paoendsuffix) AS "PAON_END_SUFFIX", 
	coalesce(initcap(l.paotext)) AS "PAON_TEXT", 
	initcap(s.streetdescription) AS "DESCRIPTION", 
	initcap(dpa.posttown) as "POSTTOWN",
	initcap(s.townname) AS "TOWN", 
	initcap(s.localityname) AS "LOC",
	'' as "DISTRICT",
	initcap(o.organisation) AS  "ORGANISATION",
	initcap(s.administrativearea) AS "COUNTY"
FROM 
	basiclandpropertyunit b
	LEFT JOIN 
	landpropertyidentifier l ON b.uprn = l.uprn
		LEFT JOIN 
		organisation o ON b.uprn = o.uprn
			LEFT JOIN 
			deliverypointaddress dpa ON b.uprn = dpa.uprn
				LEFT JOIN
				streetdescriptiveidentifier s ON l.usrn::double precision = s.usrn::double precision;
END

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION at_ls_buildfrom_addresspremium() OWNER TO postgres;
COMMENT ON FUNCTION at_ls_buildfrom_addresspremium() IS 'Reserved - Astun Technology Core Function';


-- Function: at_wkf_build_from_addresspremium_astype(character varying)

-- DROP FUNCTION at_wkf_build_from_addresspremium_astype(character varying);

CREATE OR REPLACE FUNCTION at_wkf_build_from_addresspremium_astype(character varying)
  RETURNS unknown AS
$BODY$

SELECT at_ls_buildfrom_addresspremium();
SELECT at_ls_bafn($1);
SELECT 'Finished';

$BODY$
  LANGUAGE sql VOLATILE
  COST 100;
ALTER FUNCTION at_wkf_build_from_addresspremium_astype(character varying) OWNER TO postgres;
COMMENT ON FUNCTION at_wkf_build_from_addresspremium_astype(character varying) IS 'This function builds the Address Lookup from an Address Base Plus source.

Studio Additional parameters:
''type''

PostgreSQL usage: at_wkf_build_from_addresspremium_astype($1 varchar)
$1 is the type';


-- Function: at_wkf_build_from_addresspremium()

-- DROP FUNCTION at_wkf_build_from_addresspremium();

CREATE OR REPLACE FUNCTION at_wkf_build_from_addresspremium()
  RETURNS unknown AS
$BODY$

SELECT at_wkf_build_from_addresspremium_astype('Property');
SELECT 'Finished';

$BODY$
  LANGUAGE sql VOLATILE
  COST 100;
ALTER FUNCTION at_wkf_build_from_addresspremium() OWNER TO postgres;
COMMENT ON FUNCTION at_wkf_build_from_addresspremium() IS 'This function builds the Address Lookup from an Address Base Plus source.

Studio Additional parameters:
None

PostgreSQL usage: at_wkf_build_from_addresspremium()';




-- Function: at_ls_buildfrom_addresspremium_csv()

-- DROP FUNCTION at_ls_buildfrom_addresspremium_csv();

CREATE OR REPLACE FUNCTION at_ls_buildfrom_addresspremium_csv()
  RETURNS void AS
$BODY$
BEGIN
-- Drop the temporary table
PERFORM at_sys_drop_table('tmpTableBuild');


CREATE TABLE "tmpTableBuild"
(
  "UPRN" text,
  "USRN" text,
  "EASTING" text,
  "NORTHING" text,
  "POSTCODE" text,
  "SAON_START_NUM" text,
  "SAON_START_SUFFIX" text,
  "SAON_END_NUM" text,
  "SAON_END_SUFFIX" text,
  "SAON_TEXT" text,
  "PAON_START_NUM" text,
  "PAON_START_SUFFIX" text,
  "PAON_END_NUM" text,
  "PAON_END_SUFFIX" text,
  "PAON_TEXT" text,
  "DESCRIPTION" text,
  "POSTTOWN" text,
  "TOWN" text,
  "LOC" text,
  "DISTRICT" text,
  "ORGANISATION" text,
  "COUNTY" text,
  "PostalAddress" character varying,
  "Suffix" character varying
)
WITH (
  OIDS=FALSE
);

       
INSERT INTO "tmpTableBuild" SELECT 
	uprn as "UPRN",
	usrn as "USRN", 
	st_x(wkb_geometry) as "EASTING",
	st_y(wkb_geometry) as "NORTHING",
	coalesce(postcode_locator) AS "POSTCODE",
	coalesce(sao_start_number) as "SAON_START_NUM", 
	coalesce(sao_start_suffix) as "SAON_START_SUFFIX", 
	coalesce(sao_end_number) as "SAON_END_NUM", 
	coalesce(sao_end_suffix) AS "SAON_END_SUFFIX", 
	coalesce(initcap(sao_text)) AS "SAON_TEXT", 
	coalesce(pao_start_number) AS "PAON_START_NUM", 
	coalesce(pao_start_suffix) AS "PAON_START_SUFFIX", 
	coalesce(pao_end_number) AS "PAON_END_NUM", 
	coalesce(pao_end_suffix) AS "PAON_END_SUFFIX", 
	coalesce(initcap(pao_text)) AS "PAON_TEXT", 
	initcap(street_description) AS "DESCRIPTION", 
	'' as "POSTTOWN",
	initcap(town_name) AS "TOWN", 
	initcap(locality_name) AS "LOC",
	'' as "DISTRICT",
	initcap(organisation_name) AS  "ORGANISATION",
	'' AS "COUNTY"
FROM 
	addressbase_csv_import;
	


END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION at_ls_buildfrom_addresspremium_csv() OWNER TO postgres;
COMMENT ON FUNCTION at_ls_buildfrom_addresspremium_csv() IS 'Reserved - Astun Technology Core Function';


-- Function: at_wkf_build_from_addresspremium_csv_astype(character varying)

-- DROP FUNCTION at_wkf_build_from_addresspremium_csv_astype(character varying);

CREATE OR REPLACE FUNCTION at_wkf_build_from_addresspremium_csv_astype(character varying)
  RETURNS unknown AS
$BODY$

SELECT at_ls_buildfrom_addresspremium_csv();
SELECT at_ls_bafn($1);
SELECT 'Finished';

$BODY$
  LANGUAGE sql VOLATILE
  COST 100;
ALTER FUNCTION at_wkf_build_from_addresspremium_csv_astype(character varying) OWNER TO postgres;
COMMENT ON FUNCTION at_wkf_build_from_addresspremium_csv_astype(character varying) IS 'This function builds the Address Lookup from an Address Base Plus source.

Studio Additional parameters:
''type''

PostgreSQL usage: at_wkf_build_from_addresspremium_csv_astype($1 varchar)
$1 is the type';


-- Function: at_wkf_build_from_addresspremium_csv()

-- DROP FUNCTION at_wkf_build_from_addresspremium_csv();

CREATE OR REPLACE FUNCTION at_wkf_build_from_addresspremium_csv()
  RETURNS unknown AS
$BODY$

SELECT at_wkf_build_from_addresspremium_csv_astype('Property');
SELECT 'Finished';

$BODY$
  LANGUAGE sql VOLATILE
  COST 100;
ALTER FUNCTION at_wkf_build_from_addresspremium_csv() OWNER TO postgres;
COMMENT ON FUNCTION at_wkf_build_from_addresspremium_csv() IS 'This function builds the Address Lookup from an Address Base Plus source.

Studio Additional parameters:
None

PostgreSQL usage: at_wkf_build_from_addresspremium_csv()';