· 4 years ago · Mar 01, 2021, 03:00 AM
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), double_cre_for_contract as(
155 select
156 c.id as id,
157 cre.id as cre_id,
158 ct.from_,
159 count(cp.id)
160 from
161 rtneo_contract_position cp
162 join rtneo_contragent_real_estate cre on
163 cp.contragent_real_estate_id = cre.id
164 join rtneo_contract ct on
165 cp.contract_id = ct.id
166 join rtneo_contragent c on
167 ct.contragent_id = c.id
168 join rtneo_real_estate re on
169 cre.real_estate_id = re.id
170 where cre.delete_ts is null and cp.delete_ts is null and c.delete_ts is null
171 and ct.main_contract_id is null
172 group by c.id, cre.id, ct.from_
173 having count(cp.id)>1
174 order by cre.contragent_id
175), total_temp(id) as(
176 --Объеденяем контрагентов для исключения
177 select id from exclude_cre
178 union
179 select id from contragents_pay
180 union
181 select id from renter_cre
182 union
183 select id from c_no_offers
184 union
185 select id from c_no_contract
186 union
187 select id from cre_land_plot
188 union
189 select id from fillials
190 union
191 select id from two_offer
192 union
193 select id from not_bill_21
194 union
195 select id from middle_contract
196 union
197 select id from budgetary
198 union
199 select id from double_cre_for_contract
200), total_except(id) as(
201 --Выбираем контрагентов с нежилыми, и исключаем отобранных для исключения контрагентов
202 select
203 c.id
204 from
205 rtneo_contragent c
206 join rtneo_contragent_real_estate cre on
207 c.id = cre.contragent_id
208 where
209 c.delete_ts is null and cre.delete_ts is null
210 and (cre.type_id = 'a633833e-83bc-52b1-0aef-9898739f434f'
211 or cre.type_id = 'a1122505-7e60-19f0-be56-5ec549ab80c8')
212 except
213 select
214 distinct id
215 from
216 total_temp
217 --
218)
219
220insert into t_contragents("contragent_id")
221(select te.id from total_except te)
222
223--Во временной таблице
224select count(*) from total_except t where t.contragent_id = 'dee8a59a-50d1-b086-2594-9af87ccd4186'
225--Итог по контрагентам
226select count(*) from total_except t
227
228
229--Проверка начислений по объектам
230--Метод
231create or replace function return_cre_total_sum(cre_id uuid, "period" date) returns numeric as $$
232declare
233 "sum" numeric;
234 tariff numeric;
235begin
236--
237select
238 t.tariff*1.2 as t
239from
240 rtneo_tariff t
241where t.delete_ts is null
242 and t."name" = 1
243 and "period" between t.date_since and t.date_till into tariff;
244--
245select
246 case when u.is_area is not null and u.is_area = true then
247 (rec.norm * (re.area * coalesce(cre.share_, 1)))/12 * tariff
248 else
249 (rec.norm * coalesce(cre.calculation_amount, (re.area * coalesce(cre.share_, 1))*rec.ratio))/12 * tariff
250 end
251from
252 rtneo_contragent_real_estate cre
253join rtneo_real_estate re on
254 cre.real_estate_id = re.id
255join rtneo_real_estate_category rec on
256 cre.category_id = rec.id
257join rtneo_unit u on rec.unit_id = u.id
258where
259 cre.id = cre_id
260into "sum";
261--
262return "sum";
263end $$ language plpgsql;
264
265--Запрос
266with real_estates as(
267 select
268 distinct re.id as id, re.area
269 from
270 rtneo_real_estate re
271 join rtneo_contragent_real_estate cre on
272 re.id = cre.real_estate_id
273 join rtneo_contragent c on
274 cre.contragent_id = c.id
275 join t_contragents t on
276 t.contragent_id = c.id
277 where cre.delete_ts is null
278 and (c.type_ = 1 or c.type_ = 2)
279 and (re.municipality_id = 'df5c1720-3268-0ca0-bb8d-25601b59c461'
280 or re.municipality_id = '90ee42aa-9d9e-0365-c8d1-e0b3400240ac'
281 or re.municipality_id = 'b9ecbdaa-0a2c-aa2f-8256-a85f639a50a2'
282 or re.municipality_id = '583ecc11-0c6b-77c5-f4ec-f4057489facd'
283 or re.municipality_id = '779e475d-4b7a-4e25-b461-66be0949c00a'
284 or re.municipality_id = '9690c445-31bf-4fce-4234-b9af0ad7151f')
285 and re.area is not null
286 order by re.area desc
287-- offset 0 limit 7000
288), actual_accruals as(
289 select
290 a.contragent_id as c_id,
291 a."period" as "period",
292 a.create_ts,
293 row_number() over (partition by a.contragent_id, a."period" order by a.create_ts desc) as relevance,
294 a.total_sum,
295 cre.id as cre_id,
296 cre.own_type as "owner",
297 re.cadastral_number as cad
298 from
299 rtneo_accrual a
300 join rtneo_contract_position cp on
301 a.contract_position_id = cp.id
302 join rtneo_contragent_real_estate cre on
303 cp.contragent_real_estate_id = cre.id
304 join rtneo_real_estate re on
305 cre.real_estate_id = re.id
306 --join rtneo_real_estate_category rec on
307 -- cre.category_id = rec.id
308 join rtneo_real_estate_container_yard recy on
309 cre.id = recy.contragent_real_estate_id
310 join real_estates res on
311 re.id = res.id
312 where a.delete_ts is null and cp.delete_ts is null and cre.delete_ts is null
313 --Не берем по факту!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
314 and recy.fact_request is null
315 order by a.contragent_id
316), all_re as(
317 select
318 a.c_id as c_id,
319 a."owner",
320 a.cad as cad,
321 a."period" as "period",
322 sum(a.total_sum) as "sum",
323 min(return_cre_total_sum(a.cre_id, a."period")) as total
324 from
325 actual_accruals a
326 where
327 a.relevance = 1
328 group by
329 c_id, a."owner",
330 a.cad, a."period"
331 order by
332 a.cad, a."period"
333), only_lucky as(
334 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)
335)
336select
337 distinct c.id,
338 c.personal_account,
339 c."name",
340 (
341 select
342 sum(b.sum_)
343 from
344 rtneo_bill b
345 where
346 b.delete_ts is null
347 and b.contragent_id = c.id
348 and b.relevance = true),
349 c.not_doing_business
350from
351 rtneo_contragent c
352join only_lucky o on
353 c.id = o.c_id
354where
355 (c.type_ = 1
356 or c.type_ = 2)