dAExHL7d

1--Контрагенты для суда
2
3drop table if exists t_contragents cascade;
4
5CREATE TEMP TABLE t_contragents
6(
7	"contragent_id"		uuid
8);
9
10with contragents_pay as(
11	--Уберем контрагентов с оплатами
12	select
13		c.id as id
14	from
15		rtneo_contragent c
16	join (
17		select
18			distinct rp.inn as inn
19		from
20			rtneo_payment rp) as pay on
21		c.inn = pay.inn
22	--
23),exclude_cre as(
24	--Контрагенты у которых есть в договорах исключенные объекты
25	select
26		c.contragent_id as id
27	from
28		rtneo_contract c
29	join rtneo_contract_position cp on
30		c.id = cp.contract_id
31	join rtneo_contragent_real_estate cre on
32		cp.contragent_real_estate_id = cre.id
33	where c.delete_ts is null and cp.delete_ts is null and cre.delete_ts is null
34	and (cre.exclude_from_accounting is not null and cre.exclude_from_accounting = true)
35	--
36), renter_cre as(
37	--Контрагенты с арендаторами
38	select
39		cre.contragent_id as id
40	from
41		rtneo_contragent_real_estate cre
42	left join rtneo_real_estate_renter rer on
43		cre.id = rer.contragent_real_estate_id
44	where cre.delete_ts is null and rer.delete_ts is null
45	and (rer.id is not null or cre.own_type = 1 or cre.own_type = 4) 
46--	and cre.contragent_id = 'dee8a59a-50d1-b086-2594-9af87ccd4186'
47	--
48), c_no_offers as(
49	--Контрагенты без оферт
50	select
51	--	count(distinct b.contragent_id)
52		b.contragent_id as id
53	from
54		rtneo_bill b
55	join rtneo_contract ct on
56		b.contract_id = ct.id
57	where b.delete_ts is null and ct.delete_ts is null
58	and b.relevance = true
59	and b.period_ = '2019-05-01'
60	and ct.main_contract_id is null
61	--
62), c_no_contract as(
63	--Контрагенты с объектами без договоров
64	select
65		cre.contragent_id as id
66	from
67		rtneo_contragent_real_estate cre
68	join rtneo_contract_position cp on
69		cp.contragent_real_estate_id = cre.id
70	where cp.delete_ts is null and cre.delete_ts is null
71	and (cre.exclude_from_accounting is null and cre.exclude_from_accounting = false)
72	and (cre.type_id = 'a633833e-83bc-52b1-0aef-9898739f434f' or cre.type_id = 'a1122505-7e60-19f0-be56-5ec549ab80c8')
73	and cp is null
74	--
75), cre_land_plot as(
76	--Нужно ли исключать контрагентов с земельными участками???????????????????????????????
77	select
78		cre.contragent_id as id
79	from
80		rtneo_contragent_real_estate cre
81	where
82		cre.delete_ts is null
83		and cre.type_id = 'b468fb60-2363-b06b-6e80-0f0e77ff383c'
84	--
85), fillials as(
86	--Контрагенты с филиалами
87	select
88		c.id as id
89	from
90		rtneo_contragent c
91	join (
92		select
93			c.inn as inn,
94			count(c)
95		from
96			rtneo_contragent c
97		where
98			c.delete_ts is null
99		group by
100			c.inn
101		having
102			count(c) > 1) as fil on
103		c.inn = fil.inn
104	where
105		c.delete_ts is null
106	--
107), two_offer as(
108	--2 оферты
109	select
110		ct.contragent_id as id,
111		ct.main_contract_id,
112		count(ct.id)
113	from
114		rtneo_contract ct
115	where ct.delete_ts is null 
116		and ct."template" = 'MSG310820'
117	group by
118		ct.contragent_id,
119		ct.main_contract_id
120	having
121		count(ct.id)>1
122	--
123), not_bill_21 as(
124	--Те, у кого нет начислений на 21-ый год
125	select
126		bills.c_id as id
127	from
128		(
129		select
130			c.id as c_id,
131			max(b.period_) over (partition by c.id) as max_period
132		from
133			rtneo_contragent c
134		join rtneo_bill b on
135			c.id = b.contragent_id
136		where
137			b.delete_ts is null) as bills
138	where bills.max_period < '2021-01-01'
139	--
140), middle_contract as(
141	--Контрагенты с договорами по среднему
142	select
143		ct.contragent_id as id
144	from
145		rtneo_contract_position cp
146	join rtneo_contract ct on
147		cp.contract_id = ct.id
148	where
149		cp.delete_ts is null
150		and cp.contragent_real_estate_id is null
151	--
152), budgetary as(
153	select c.id as id from rtneo_contragent c where c.category_id = '22381c77-e556-953b-f83a-5069b89cf072'
154), total_temp(id) as(
155	--Объеденяем контрагентов для исключения
156	select id from exclude_cre
157	union
158	select id from contragents_pay
159	union
160	select id from renter_cre
161	union
162	select id from c_no_offers
163	union
164	select id from c_no_contract
165	union
166	select id from cre_land_plot
167	union
168	select id from fillials
169	union
170	select id from two_offer
171	union
172	select id from not_bill_21
173	union
174	select id from middle_contract
175	union
176	select id from budgetary
177), total_except(id) as(
178	--Выбираем контрагентов с нежилыми, и исключаем отобранных для исключения контрагентов
179	select
180		c.id
181	from
182		rtneo_contragent c
183	join rtneo_contragent_real_estate cre on
184		c.id = cre.contragent_id
185	where
186		c.delete_ts is null	and cre.delete_ts is null
187		and (cre.type_id = 'a633833e-83bc-52b1-0aef-9898739f434f'
188			or cre.type_id = 'a1122505-7e60-19f0-be56-5ec549ab80c8')
189	except
190	select
191		distinct id
192	from
193		total_temp
194	--
195)
196
197insert into t_contragents("contragent_id")
198(select te.id from total_except te)
199
200--Во временной таблице
201select count(*) from total_except t where t.contragent_id = 'dee8a59a-50d1-b086-2594-9af87ccd4186'
202--Итог по контрагентам
203select count(*) from total_except t
204
205
206--Проверка начислений по объектам
207--Метод
208create or replace function return_cre_total_sum(cre_id uuid, "period" date) returns numeric as $$
209declare
210	"sum" numeric;
211	tariff numeric;
212begin
213--	
214select
215	t.tariff*1.2 as t
216from
217	rtneo_tariff t
218where t.delete_ts is null
219	and t."name" = 1
220	and "period" between t.date_since and t.date_till into tariff;
221--
222select
223	case when u.is_area is not null and u.is_area = true then
224		(rec.norm * (re.area * coalesce(cre.share_, 1)))/12 * tariff
225	else
226		(rec.norm * coalesce(cre.calculation_amount, (re.area * coalesce(cre.share_, 1))*rec.ratio))/12 * tariff
227	end
228from
229	rtneo_contragent_real_estate cre
230join rtneo_real_estate re on
231	cre.real_estate_id = re.id
232join rtneo_real_estate_category rec on
233	cre.category_id = rec.id
234join rtneo_unit u on rec.unit_id = u.id
235where
236	cre.id = cre_id
237into "sum";
238--
239return "sum";
240end $$ language plpgsql;
241
242--Запрос
243with real_estates as(
244	select
245		distinct re.id as id, re.area
246	from
247		rtneo_real_estate re
248	join rtneo_contragent_real_estate cre on
249		re.id = cre.real_estate_id
250	join rtneo_contragent c on
251		cre.contragent_id = c.id
252	join t_contragents t on
253		t.contragent_id = c.id
254	where cre.delete_ts is null
255	and (c.type_ = 1 or c.type_ = 2)	
256	and (re.municipality_id = 'df5c1720-3268-0ca0-bb8d-25601b59c461'
257	or re.municipality_id = '90ee42aa-9d9e-0365-c8d1-e0b3400240ac'
258	or re.municipality_id = 'b9ecbdaa-0a2c-aa2f-8256-a85f639a50a2'
259	or re.municipality_id = '583ecc11-0c6b-77c5-f4ec-f4057489facd'
260	or re.municipality_id = '779e475d-4b7a-4e25-b461-66be0949c00a'
261	or re.municipality_id = '9690c445-31bf-4fce-4234-b9af0ad7151f')
262	and re.area is not null
263	order by re.area desc
264--		offset 0 limit 7000
265), actual_accruals as(
266	select
267		a.contragent_id as c_id,
268		a."period" as "period",
269		a.create_ts,
270		row_number() over (partition by a.contragent_id, a."period" order by a.create_ts desc) as relevance,
271		a.total_sum,
272		cre.id as cre_id,
273		cre.own_type as "owner",
274		re.cadastral_number as cad
275	from
276		rtneo_accrual a
277	join rtneo_contract_position cp on
278		a.contract_position_id = cp.id
279	join rtneo_contragent_real_estate cre on
280		cp.contragent_real_estate_id = cre.id
281	join rtneo_real_estate re on
282		cre.real_estate_id = re.id
283	--join rtneo_real_estate_category rec on
284	--	cre.category_id = rec.id
285	join rtneo_real_estate_container_yard recy on
286		cre.id = recy.contragent_real_estate_id
287	join real_estates res on
288		re.id = res.id
289	where a.delete_ts is null and cp.delete_ts is null and cre.delete_ts is null
290	--Не берем по факту!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
291		and recy.fact_request is null
292	order by a.contragent_id
293), all_re as(
294	select
295		a.c_id as c_id,
296		a."owner",
297		a.cad as cad,
298		a."period" as "period",
299		sum(a.total_sum) as "sum",
300		min(return_cre_total_sum(a.cre_id, a."period")) as total
301	from
302		actual_accruals a
303	where
304		a.relevance = 1
305	group by
306		c_id, a."owner",
307		a.cad, a."period"
308	order by
309		a.cad, a."period"
310), only_lucky as(
311	select a.c_id as c_id from all_re a where a."sum" = a.total or ((a.total - a."sum")<1.00 and (a.total - a."sum")>-1.00)
312)
313select
314	distinct c.id,
315	c.personal_account,
316	c."name",
317	(
318	select
319		sum(b.sum_)
320	from
321		rtneo_bill b
322	where
323		b.delete_ts is null
324		and b.contragent_id = c.id
325		and b.relevance = true),
326	c.not_doing_business
327from
328	rtneo_contragent c
329join only_lucky o on
330	c.id = o.c_id
331where
332	(c.type_ = 1
333	or c.type_ = 2)