main_dashboard.appeals(reg)
Y
Normal
0
N
1000
100
-
2024/08/02 11:56:22.507
-
2024/08/02 11:56:22.507
Get variables 3 2
Select values 3 2
N
Select values 3 2
Insert / update (main_dashboard.appeals) 2
N
Table input (appeal_document) регионы
Get variables 3 2
N
Execute SQL script
Table input (appeal_document) регионы
Y
Insert / update (main_dashboard.appeals) 2
Execute SQL script 2
Y
Table input (appeal_document) регионы
Insert / update (main_dashboard.appeals) 2
Y
Execute SQL script
ExecSql
N
1
none
ervu-dashboard
N
N
N
N
N
DELETE FROM main_dashboard.appeals
WHERE recording_date = current_date
AND schema = 'Organization'
384
192
Execute SQL script 2
ExecSql
N
1
none
ervu-dashboard
N
N
N
N
N
DELETE FROM main_dashboard.appeals
WHERE recording_date = current_date
AND schema = 'REG_LVL'
1152
192
Get variables 3 2
GetVariable
Y
1
none
-1
REG_ID
-1
none
String
-1
VK_ARRAY
-1
none
String
-1
SCM
-1
none
String
688
256
Insert / update (main_dashboard.appeals) 2
InsertUpdate
Y
1
none
100
ervu-dashboard
=
recruitment_id
recruitment_id
=
schema
schema
=
recording_date
recording_date
main_dashboard
total_appeals
total_appeals
Y
average_consideration
average_consideration
Y
resolved
resolved
Y
average_rating
average_rating
Y
average_to_face
average_to_face
Y
"average_EPGU"
average_epgu
Y
average_to_face_percent
average_to_face_percent
Y
"average_EPGU_percent"
average_epgu_percent
Y
recruitment_id
recruitment_id
N
schema
schema
N
Y
912
192
Select values 3 2
SelectValues
Y
1
none
N
REG_ID
REG_ID
String
-2
-2
false
false
VK_ARRAY
VK_ARRAY
String
-2
-2
false
false
SCM
SCM
String
-2
-2
false
false
832
256
Table input (appeal_document) регионы
TableInput
N
1
none
ervu-dashboard
N
0
WITH
ids AS (
SELECT unnest(string_to_array(trim(both '{}' from '${ARR_REG}'), ','))::uuid AS recruitment_id
),
base_data AS (
SELECT
region_id,
solution_type,
CASE
WHEN incoming_date <> '-' THEN incoming_date::date
ELSE NULL
END AS incoming_date,
CASE
WHEN fact_end_date <> '-' THEN fact_end_date::date
ELSE NULL
END AS fact_end_date,
CASE
WHEN response_evalution <> '-' THEN response_evalution::NUMERIC -- оценка ответа заявителем
ELSE 0
END AS response_evalution,
source
FROM appeals.appeals_list al
WHERE region_id::uuid IN (SELECT recruitment_id FROM ids)
),
aggregated_data AS (
SELECT
region_id AS recruitment_id,
COUNT(*) AS total_appeals,
COUNT(*) FILTER (WHERE solution_type = 'Решено') AS resolved,
-- Среднее время рассмотрения, учитывая возможные NULL для дат
ROUND(AVG(CASE
WHEN fact_end_date IS NOT NULL AND incoming_date IS NOT NULL
THEN fact_end_date::DATE - incoming_date::DATE
ELSE NULL
END), 1) AS average_consideration,
-- Средний рейтинг, учитывая 0 для некорректных значений
ROUND(AVG(response_evalution), 1) AS average_rating,
COUNT(*) FILTER (WHERE source = 'ПОС') AS total_pos,
COUNT(*) FILTER (WHERE source = 'ЕПГУ') AS total_epgu
FROM base_data
GROUP BY region_id
)
SELECT
i.recruitment_id,
current_date AS recording_date,
'Organization' AS schema,
COALESCE(ad.total_appeals, 0) AS total_appeals,
COALESCE(ad.resolved, 0) AS resolved,
COALESCE(ad.average_consideration, 0) AS average_consideration,
COALESCE(ad.average_rating, 0) AS average_rating,
COALESCE(ad.total_pos, 0) AS average_to_face,
COALESCE(ad.total_epgu, 0) AS average_epgu,
COALESCE(ROUND(ad.total_pos::NUMERIC * 100 / NULLIF(ad.total_appeals, 0), 1), 0) AS average_to_face_percent,
COALESCE(ROUND(ad.total_epgu::NUMERIC * 100 / NULLIF(ad.total_appeals, 0), 1), 0) AS average_epgu_percent
FROM ids i
LEFT JOIN aggregated_data ad ON ad.recruitment_id = i.recruitment_id;
Y
592
192