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;