--select * from dem.v_external_ids4identity

CREATE or replace FUNCTION bill.get_inv_recv_identity (integer) RETURNS integer AS $$
select value from ( 
select id.pk_id, id.value::integer  
from dem.v_external_ids4identity id
join dem.identity b on ( id.value = b.pk::text )
 where id.pk_type = 18 and id.pk_identity= $1 
 union all select 0,$1
) me order by pk_id desc 
limit 1;
$$ LANGUAGE SQL;

 

drop function bill.get_inv_address(integer);

/*
CREATE or replace FUNCTION bill.get_inv_address (integer) RETURNS text AS $$
   
select label1_3::text from (select  lastnames || ', '|| firstnames || '\n' 
                 || street   || ' ' || number|| coalesce(subunit,'')||'\n'
                  || code_country || '-' || postcode || ' ' || urb as label1_3,
                   address_type  from dem.v_pat_addresses where pk_identity=$1 
                   union all
                   select lastnames||', '|| firstnames|| '\n' || 'in Praxis', 'zzz'
                   from  dem.v_basic_person where pk_identity=$1
                   
                   ) me
                order by address_type='bill' desc limit 1
                ;
                
$$ LANGUAGE SQL;
*/
CREATE OR REPLACE FUNCTION bill.get_inv_address(integer)
  RETURNS integer -- NULL if no suitable address available
  AS
$BODY$

select pk_address from 
dem.v_pat_addresses where pk_identity=$1
  and address_type in ( 'home', 'invoice')
order by address_type='invoice' desc, pk_address desc
limit 1
;

$BODY$
  LANGUAGE sql
  
;


/*

select id.pk, x.lastnames, x.firstnames, bill.get_inv_recv_identity (id.pk), 
 bill.get_inv_address (bill.get_inv_recv_identity (id.pk))
from dem.identity id
join  dem.v_basic_person x on ( id.pk = x.pk_identity)
 where bill.get_inv_address (bill.get_inv_recv_identity (id.pk))
  not like x.lastnames ||'%'
order by id.pk
;

*/



-- DROP FUNCTION de_de.fmt_inv_address(integer,integer);

CREATE OR REPLACE FUNCTION de_de.fmt_inv_address(integer)
-- argument is identity
  RETURNS text AS
$BODY$
   
select label1_3::text from (select  lastnames || ', '|| firstnames || E'\n' 
                 || street   || ' ' || number|| coalesce(subunit,'')||E'\n'
                  || code_country || '-' || postcode || ' ' || urb as label1_3,
                   address_type  from dem.v_pat_addresses where pk_address=bill.get_inv_address($1)
                   union all
                   select lastnames||', '|| firstnames|| E'\n' || 'in Praxis', 'zzz'
                   from  dem.v_basic_person where pk_identity=$1
                   
                   ) me
                order by address_type='invoice' desc limit 1
                ;
                
$BODY$
  LANGUAGE sql ;




-- select * from dem.v_pat_addresses where pk_identity=15 ;

select pk,  bill.get_inv_address(pk), --bill.get_inv_address_pk(pk),
   de_de.fmt_inv_address(pk)
 from dem.identity 
;




--insert into i18n.translations ( lang, orig,trans  )
-- values ( 'de_DE', 'fmt_inv_address', 'de_de.fmt_inv_address' )



