qKUptFCc

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