· 4 years ago · Jan 28, 2021, 09:58 AM
1CREATE OR REPLACE FUNCTION repfun.x5_tth_opt(dt1 date, dt2 date, date_type character varying, macro_ref_ids character varying[], cluster_ref_ids character varying[], org_unit_ids character varying[], is_external_candidate boolean)
2 RETURNS TABLE(mnth date, macro_region character varying, cluster character varying, funnel_name character varying, vacancy_name character varying, fio character varying, max_new timestamp without time zone, priem_date timestamp without time zone, actual_set_at timestamp without time zone, oform_start timestamp without time zone, tth1 numeric, tth2 numeric, tth3 numeric, extra_profile_name character varying, extra_org_unit_name text)
3 LANGUAGE plpgsql
4AS $function$
5declare
6main_query text;
7add_joins text;
8part_before_add_joins text;
9part_after_add_joins text;
10dates_by_new text;
11dates_by_status text;
12begin
13
14add_joins := '';
15
16if array_length(macro_ref_ids, 1) is not null
17 then drop table if exists repfun_x5_tth_filter_macro;
18 create temporary table repfun_x5_tth_filter_macro as
19 select unnest(macro_ref_ids) as macro_ref_ids;
20 alter table repfun_x5_tth_filter_macro owner to reader;
21 add_joins := add_joins || 'join repfun_x5_tth_filter_macro mf on mf.macro_ref_ids = ci.macro_id ';
22end if;
23
24if array_length(cluster_ref_ids, 1) is not null
25 then drop table if exists repfun_x5_tth_filter_cluster;
26 create temporary table repfun_x5_tth_filter_cluster as
27 select unnest(cluster_ref_ids) as cluster_ids;
28 alter table repfun_x5_tth_filter_cluster owner to reader;
29 add_joins := add_joins || 'join repfun_x5_tth_filter_cluster clf on clf.cluster_ids = ci.cluster_id ';
30end if;
31
32if array_length(org_unit_ids, 1) is not null
33 then drop table if exists repfunx_x5_tth_filter_ou;
34 create temporary table repfunx_x5_tth_filter_ou as
35 select unnest(org_unit_ids) as org_units;
36 alter table repfunx_x5_tth_filter_ou owner to reader;
37 add_joins := add_joins || 'join repfunx_x5_tth_filter_ou fou on fou.org_units = ci.org_unit_id ';
38end if;
39
40dates_by_new := '';
41
42if date_type = 'new'
43 then dates_by_new := ' and ld.max_new >= ''' || dt1 || '''
44 and ld.max_new < ''' || dt2 || '''::timestamp + interval ''1 day''';
45end if;
46
47dates_by_status := '';
48
49if date_type = 'offerRegistrationHasBegun'
50 then dates_by_status := ' and oform_start >= ''' || dt1 || '''
51 and oform_start < ''' || dt2 || '''::timestamp + interval ''1 day ''';
52elsif date_type = 'employment'
53 then dates_by_status := ' and priem_date >= ''' || dt1 || '''
54 and priem_date < ''' || dt2 || '''::timestamp + interval ''1 day''';
55end if;
56
57part_before_add_joins := '
58with last_date as(
59 select
60 candidate_id,
61 max(set_at)over(partition by candidate_id) as max_set,
62 max(set_at)filter(where status_id = ''new'')over(partition by candidate_id) as max_new
63 from statuses
64),
65
66dates as(
67 select distinct
68 x5mv.candidate_id,
69 ld.max_new,
70 max(x5mv.set_at)filter(where status_id = ''offerRegistrationHasBegun'')over(partition by x5mv.candidate_id) as oform_start,
71 max(x5mv.set_at)filter(where status_id = ''employment'')over(partition by x5mv.candidate_id) as priem_date,
72 ld.max_set as last_date
73 from x5_can_info_mv x5mv
74 join last_date ld using (candidate_id)
75 where x5mv.set_at < ''' || dt2 || '''::timestamp + interval ''1 day''
76 ' || dates_by_new || '
77),
78
79can_info as(
80 select
81 can.id as candidate_id,
82 can.fio,
83 mcr.display_name as macro_region,
84 cl.display_name as cluster,
85 cl.id as cluster_id,
86 mcr.id as macro_id,
87 vac.display_name as vacancy_name,
88 vac.funnel_id,
89 fun.funnel_name,
90 hr.org_unit_id,
91 (can_ed.extra_data ->> ''Profile'')::character varying as extra_profile_name,
92 (can_ed.extra_data ->> ''OrgUnit'')::text as extra_org_unit_name
93 from candidates can
94 join vacancies vac on vac.id = can.vacancy_id
95 join hire_requests hr on hr.id = can.job_requisition_id
96 join extra_data ed on ed.source_id = hr.id
97 and ed.source_type = ''hire_requests''
98 join extra_data can_ed on can_ed.source_id = can.id
99 and can_ed.source_type = ''candidates_info''
100 join funnels fun on fun.funnel_id = vac.funnel_id
101 left join reference_values mcr on mcr.id = ed.extra_data ->> ''Macroregion''
102 and mcr.company_id = ''x5Company''
103 and mcr.reference_type = ''Macroregion''
104 left join reference_values cl on cl.id = ed.extra_data ->> ''Cluster''
105 and cl.company_id = ''x5Company'' and cl.reference_type = ''Cluster''
106 where true and hr.company_id = ''x5Company''
107)
108
109select distinct
110 case
111 when ''' || date_type || ''' = ''new''
112 then date_trunc(''month'', max_new)::date
113 when ''' || date_type || ''' = ''offerRegistrationHasBegun''
114 then date_trunc(''month'', d.oform_start)::date
115 when ''' || date_type || ''' = ''employment''
116 then date_trunc(''month'', d.priem_date)::date
117 end as mnth,
118 ci.macro_region,
119 ci.cluster,
120 ci.funnel_name,
121 ci.vacancy_name,
122 ci.fio,
123 d.max_new,
124 d.priem_date,
125 d.last_date as actual_set_at,
126 d.oform_start,
127 case when ((extract(epoch from d.priem_date) - extract(epoch from d.max_new)) / (60*60*24))::numeric(18,1) < 0
128 then 1
129 else ((extract(epoch from d.priem_date) - extract(epoch from d.max_new)) / (60*60*24))::numeric(18,1) end as tth1,
130 case when ((extract(epoch from d.last_date) - extract(epoch from d.max_new)) / (60*60*24))::numeric(18,1) < 0
131 then 1
132 else ((extract(epoch from d.last_date) - extract(epoch from d.max_new)) / (60*60*24))::numeric(18,1) end as tth2,
133 case when ((extract(epoch from d.oform_start) - extract(epoch from d.max_new)) / (60*60*24))::numeric(18,1) < 0
134 then 1
135 else ((extract(epoch from d.oform_start) - extract(epoch from d.max_new)) / (60*60*24))::numeric(18,1) end as tth3,
136 ci.extra_profile_name,
137 ci.extra_org_unit_name
138from can_info ci
139join dates d on d.candidate_id = ci.candidate_id
140';
141
142part_after_add_joins := ' where true ' || dates_by_status || ' ';
143
144main_query := part_before_add_joins || add_joins || part_after_add_joins;
145
146raise notice 'main query is (%)', main_query;
147
148return query
149
150execute main_query;
151
152end;
153$function$