# создаем подзапрос
sales = Own.query(Own.thing_id, func.date(Own.date_start).label('dt')) \
.join(Ln, and_(Ln.id == Own.thing_id,
Ln.status.in_(statuses),
Own.date_start < period_to + timedelta(1),
Own.is_deleted == False,
Own.old_owner_id != None
)
) \
.join(Lg, and_(Lg.id == Own.owner_id,
Lg.is_service_company == True))
# в зависимости от условий - фильтруем
if without_online is True:
sales = sales.filter(Ln.office_id != self.online_office)
if self.mfo_ids:
sales = sales.filter(Own.old_owner_id.in_(self.mfo_ids))
sales = sales.subquery()
hist = Hist.query() \
.join(sales, and_(Hist.thing_id == sales.c.thing_id,
Hist.history_date == sales.c.dt,
Hist.history_date >= period_from,
Hist.history_date <= period_to,
Hist.is_deleted == False
)
) \
.outerjoin(AHist, AHist.thing_history_id == Hist.id)SELECT
sum(main_thinghistory.summ_body) AS body,
sum(
CASE
WHEN
(main_annuityhistory.percents_past_left IS NOT NULL)
THEN main_annuityhistory.percents_past_left + main_thinghistory.percents + main_thinghistory.percents_expired_left
ELSE main_thinghistory.percents
END
) AS percents,
sum(main_thinghistory.penalty) AS penalty
FROM main_thinghistory
JOIN
(
SELECT main_thingownershiphistory.thing_id AS thing_id, date(main_thingownershiphistory.date_start) AS dt
FROM main_thingownershiphistory
JOIN main_thing
ON main_thing.id = main_thingownershiphistory.thing_id
AND main_thing.status IN (%(status_1)s, %(status_2)s, %(status_3)s)
AND main_thingownershiphistory.date_start < %(date_start_1)s
AND main_thingownershiphistory.is_deleted = false
AND main_thingownershiphistory.old_owner_id IS NOT NULL
JOIN main_legalperson
ON main_legalperson.id = main_thingownershiphistory.owner_id
AND main_legalperson.is_service_company = true
WHERE main_thing.office_id != %(office_id_1)s
) AS anon_1
ON main_thinghistory.thing_id = anon_1.thing_id
AND main_thinghistory.history_date = anon_1.dt
AND main_thinghistory.history_date >= %(history_date_1)s
AND main_thinghistory.history_date <= %(history_date_2)s
AND main_thinghistory.is_deleted = false
LEFT OUTER JOIN main_annuityhistory ON main_annuityhistory.thing_history_id = main_thinghistory.id;