103 lines
3.4 KiB
Python
103 lines
3.4 KiB
Python
from db import dbGetMany
|
|
import datetime
|
|
from loguru import logger
|
|
|
|
def perform(dbh, params):
|
|
try:
|
|
year = params['year']
|
|
except KeyError:
|
|
year = datetime.datetime.today().year
|
|
startDate = datetime.datetime(year, 1, 1, 0, 0, 0)
|
|
endDate = datetime.datetime(year, 12, 31, 23, 59, 59)
|
|
premises = (1, 2)
|
|
|
|
|
|
# get flat tenants by object and timespan with paid overhead and due overhead
|
|
tenants = dbGetMany(
|
|
dbh,
|
|
{
|
|
"statement":
|
|
"""
|
|
select t.id as tenant_id,
|
|
t.firstname as tenant_firstname,
|
|
t.lastname as tenant_lastname,
|
|
f.id as flat_id,
|
|
f.description as flat,
|
|
p.id as house_id,
|
|
p.description as house,
|
|
ty.startdate as startdate,
|
|
ty.enddate as enddate
|
|
from tenant_t t,
|
|
premise_t p,
|
|
flat_t f,
|
|
tenancy_t ty
|
|
where ty.tenant = t.id and
|
|
ty.flat = f.id and
|
|
ty.startdate >= %(startDate)s and
|
|
(ty.enddate <= %(endDate)s or ty.enddate is null) and
|
|
f.premise = p.id and
|
|
p.id in %(premises)s
|
|
order by house_id, tenant_id
|
|
""",
|
|
"params": {
|
|
"startDate": startDate,
|
|
"endDate": endDate,
|
|
"premises": premises
|
|
}
|
|
}
|
|
)
|
|
logger.info(f"{tenants=}")
|
|
|
|
|
|
# get overhead sums by object, category and timespan
|
|
overheadSums = dbGetMany(
|
|
dbh,
|
|
{
|
|
"statement":
|
|
"""
|
|
select sum(ae.amount) as sum,
|
|
aec.description as category,
|
|
p.id as house_id,
|
|
p.description as house
|
|
from account_t a,
|
|
premise_t p,
|
|
account_entry_t ae,
|
|
account_entry_category_t aec
|
|
where p.account = a.id and
|
|
ae.account = a.id and
|
|
aec.overhead_relevant = 't' and
|
|
ae.account_entry_category = aec.id and
|
|
created_at between %(startDate)s and %(endDate)s and
|
|
p.id in %(premises)s
|
|
group by house_id, house, category
|
|
union
|
|
select 0 as sum,
|
|
aec.description as category,
|
|
p.id as house_id,
|
|
p.description as house
|
|
from account_t a,
|
|
premise_t p,
|
|
account_entry_t ae,
|
|
account_entry_category_t aec
|
|
where p.account = a.id and
|
|
ae.account = a.id and
|
|
aec.overhead_relevant = 't' and
|
|
aec.id not in (select distinct account_entry_category from account_entry_t) and
|
|
created_at between %(startDate)s and %(endDate)s and
|
|
p.id in %(premises)s
|
|
group by house_id, house, category
|
|
order by house_id, category
|
|
""",
|
|
"params": {
|
|
"startDate": startDate,
|
|
"endDate": endDate,
|
|
"premises": premises
|
|
}
|
|
}
|
|
)
|
|
logger.info(f"{overheadSums=}")
|
|
|
|
|
|
|
|
|