--Проверка начислений по объектам

--select cp from rtneo_contract_position cp where cp.contract_id = 'ab1f5a54-8e07-9a92-f114-a5ac790ce732'

create or replace function return_cre_total_sum(cre_id uuid, "period" date) returns numeric as $$

declare

"sum" numeric;

tariff numeric;

begin

select

t.tariff*1.2 as t

from

rtneo_tariff t

where t.delete_ts is null

and t."name" = 1

and "period" between t.date_since and t.date_till into tariff;

select

case when u.is_area is not null and u.is_area = true then

(rec.norm * (re.area * coalesce(cre.share_, 1)))/12 * tariff

else

(rec.norm * coalesce(cre.calculation_amount, (re.area * coalesce(cre.share_, 1))*rec.ratio))/12 * tariff

end

from

rtneo_contragent_real_estate cre

join rtneo_real_estate re on

cre.real_estate_id = re.id

join rtneo_real_estate_category rec on

cre.category_id = rec.id

join rtneo_unit u on rec.unit_id = u.id

where

cre.id = cre_id

into "sum";

return "sum";

end $$ language plpgsql;

with real_estate as(

select r.cadastral_number as cad from rtneo_real_estate r offset 0 limit 1

), actual_accruals as(

select

a.contragent_id as c_id,

a."period" as "period",

a.create_ts,

row_number() over (partition by a.contragent_id, a."period" order by a.create_ts desc) as relevance,

a.total_sum,

cre.id as cre_id,

cre.own_type as "owner",

re.cadastral_number as cad

from

rtneo_accrual a

join rtneo_contract_position cp on

a.contract_position_id = cp.id

join rtneo_contragent_real_estate cre on

cp.contragent_real_estate_id = cre.id

join rtneo_real_estate re on

cre.real_estate_id = re.id

join rtneo_real_estate_category rec on

cre.category_id = rec.id

where a.delete_ts is null and cp.delete_ts is null and cre.delete_ts is null

and re.cadastral_number = '38:36:000034:22582'

-- and re.id in (select r.id from rtneo_real_estate r offset 0 limit 1000)

order by a.contragent_id

), all_re as(

select

-- a.c_id as c_id,

-- a."owner",

a.cad as cad,

a."period" as "period",

sum(a.total_sum) as "sum",

min(return_cre_total_sum(a.cre_id, a."period")) as total

from

actual_accruals a

where

a.relevance = 1

group by

-- c_id, a."owner",

a.cad, a."period"

order by

a.cad, a."period"

)

select *, a.total - a."sum" as diff from all_re a where a."sum" <> a.total