39 lines
948 B
SQL
39 lines
948 B
SQL
create or replace view zahlung_forderung_v1 as
|
|
select
|
|
'zahlung' as zf_type,
|
|
datum_soll,
|
|
datum_ist,
|
|
id,
|
|
ROUND(betrag, 2) as betrag_zahlung,
|
|
NULL as betrag_forderung,
|
|
kommentar,
|
|
mieter
|
|
from zahlung
|
|
union
|
|
select
|
|
'forderung' as zf_type,
|
|
datum as datum_soll,
|
|
'-' as datum_ist,
|
|
id,
|
|
NULL as betrag_zahlung,
|
|
ROUND(betrag, 2) as betrag_forderung,
|
|
kommentar,
|
|
mieter
|
|
from forderung;
|
|
|
|
create or replace view zahlung_forderung as
|
|
select
|
|
*
|
|
from zahlung_forderung_v1
|
|
order by datum_soll, zf_type;
|
|
|
|
|
|
grant select on zahlung_forderung to 'hausverwaltung-ui'@'%';
|
|
|
|
|
|
alter table forderung
|
|
add constraint unique key uk_forderung_mieter_betrag_kommentar (mieter, betrag, kommentar);
|
|
|
|
alter table zahlung
|
|
add constraint unique key uk_zahlung_mieter_betrag_kommentar (mieter, betrag, kommentar);
|