evdF4w6N

1/*view для получения всех сигналов за последние 90-дней*/
2----------------------------------------------------------------------------------	
3do $do$
4begin
5if not exists(
6select
7	schemaname as schema_name,
8	matviewname as view_name,
9	matviewowner as owner,
10	ispopulated as is_populated,
11	definition
12from
13	pg_matviews
14order by
15	schema_name,
16	view_name
17
18) 
19then create materialized view signals_last_3_months as
20select
21	*
22from
23	signal_log as sl
24where
25	sl.datetime > (
26	select
27		cast((select now() - interval '90 DAYS') as date)) with no data;
28end if;
29end $do$;
30/*необходимо выполнение по расписанию(1 раз в день)*/
31----------------------------------------------------------------------------------
32refresh materialized view  signals_last_3_months;
33----------------------------------------------------------------------------------
34-- signal_type = 0 (охранный сигнал)
35-- signal_type = 1 (тестовый сигнал)
36-- signal_type = 2 (охранный сигнал или тестовый сигнал)
37-- любое другое число (все сигналы)
38create or replace function get_lost_singals_info(start_date date, end_date date, signal_type int)
39 returns table 
40 (
41 	id bigint,
42 	object_number int,
43 	city_id int,
44 	device_number int,
45 	firmware int,
46 	securige_type int,
47 	protocol_type int,
48 	device_system int,
49 	gbr_timer int,
50 	gbr_id int,
51 	kvituserid int,
52 	unicom int,
53 	sectionbarierbollid int,
54 	num_losts bigint
55 ) as $$
56 declare 
57 	query_to_execute text;
58begin		
59	query_to_execute:= '
60		select  max(sl1.id), sl1.object_number as object_number, max(city_id) as city_id, max(sl1.device_number) as device_number, max(sl1.firmware) as firmware
61		,max(sl1.securige_type) as securige_type, max(sl1.protocol_type) as protocol_type, max(sl1.device_system) as device_system
62		,max(sl1.gbr_timer) as gbr_timer,max(sl1.gbr_id) as gbr_id, max(sl1.kvituserid) as kvituserid
63		,max(sl1.unicom) as unicom, max(sl1.sectionbarierbollid) as sectionbarierbollid, count(sl1.id) as num_losts
64		from signals_last_3_months sl1
65		where -signal_type-
66		sl1.device_number is not null
67		and sl1.datetime between ''-start_date-'' and ''-end_date-''
68		group by sl1.object_number, sl1.device_number
69		having count(sl1.id) > 0';
70	
71	if signal_type = 0 then
72		query_to_execute:= REPLACE(query_to_execute, '-signal_type-', 'sl1.propazhaohrannogosignala = true and' );
73	elsif signal_type = 1 then
74		query_to_execute:= REPLACE(query_to_execute, '-signal_type-', 'sl1.propazhatestovogosignala = true and' );
75	elsif signal_type = 2 then
76		query_to_execute:= REPLACE(query_to_execute, '-signal_type-', 'sl1.propazhatestovogosignala = true or sl1.propazhaohrannogosignala = true and' );
77	else
78		query_to_execute:= REPLACE(query_to_execute, '-signal_type-', '' );
79	end if;
80	
81	query_to_execute:= REPLACE(query_to_execute, '-start_date-', to_char(start_date, 'YYYY-MM-DD'));
82	query_to_execute:= REPLACE(query_to_execute, '-end_date-', to_char(end_date, 'YYYY-MM-DD'));
83		
84	return query
85	execute query_to_execute;
86END; $$ 
87LANGUAGE 'plpgsql';
88----------------------------------------------------------------------------------