93 lines
2.4 KiB
SQL
93 lines
2.4 KiB
SQL
create or replace view account_statement_v as
|
|
select ae.id as id,
|
|
ae.description as description,
|
|
ae.created_at::timestamp::date as created_at,
|
|
ae.amount as amount,
|
|
ae.document_no as document_no,
|
|
ae.fiscal_year as fiscal_year,
|
|
aec.description as category,
|
|
ac.description as account,
|
|
ae.is_reference as is_reference,
|
|
bac.description as base_account
|
|
from joined_account_entry_v ae,
|
|
account_entry_category_t aec,
|
|
account_t ac,
|
|
account_t bac
|
|
where ae.account_entry_category = aec.id and
|
|
ae.account = ac.id and
|
|
ae.base_account = bac.id and
|
|
ac.id = 1000
|
|
order by created_at;
|
|
|
|
grant select on account_statement_v to hv2;
|
|
|
|
|
|
|
|
|
|
create or replace view income_v as
|
|
select f.description as flat,
|
|
p.description as premise,
|
|
ae.amount as amount,
|
|
ty.id as tenancy_id,
|
|
t.lastname as tenant
|
|
|
|
|
|
from flat_t f,
|
|
premise_t p,
|
|
tenancy_t ty,
|
|
tenant_t t,
|
|
account_entry_t ae,
|
|
account_entry_category_t aec
|
|
|
|
where p.id = f.premise and
|
|
f.id = ty.flat and
|
|
t.id = ty.tenant and
|
|
ae.id = t.account and
|
|
aec.id = ae.account_entry_category and
|
|
aec.description = 'Mietzahlung' and
|
|
ae.fiscal_year = 2021
|
|
;
|
|
|
|
|
|
select f.description as flat,
|
|
p.description as premise,
|
|
--ty.id as tenancy_id,
|
|
--t.lastname || ', ' || t.firstname as tenant,
|
|
--a.description as account,
|
|
aec.description as category,
|
|
sum(ae.amount) as amount
|
|
from flat_t f,
|
|
premise_t p,
|
|
tenancy_t ty,
|
|
tenant_t t,
|
|
account_t a,
|
|
account_entry_t ae,
|
|
account_entry_category_t aec
|
|
where p.id = f.premise and
|
|
ty.flat = f.id and
|
|
ty.tenant = t.id and
|
|
a.id = t.account and
|
|
ae.account = a.id and
|
|
aec.id = ae.account_entry_category and
|
|
ae.fiscal_year = 2021 and
|
|
aec.description in ('Mietzahlung', 'Mietforderung', 'Betriebskostenforderung')
|
|
|
|
group by p.description,
|
|
f.description,
|
|
aec.description
|
|
;
|
|
|
|
|
|
|
|
select aec.overhead_relevant as overhead_relevant,
|
|
aec.description as category,
|
|
sum(ae.amount) as amount
|
|
from account_entry_category_t aec,
|
|
account_entry_t ae
|
|
where aec.id = ae.account_entry_category and
|
|
aec.id not in (2, 3, 4, 29) and
|
|
ae.fiscal_year = 2021
|
|
group by aec.overhead_relevant,
|
|
aec.description
|
|
order by aec.overhead_relevant;
|