spouse_once
Y
Normal
N
1000
100
-
2024/08/15 14:02:51.713
-
2024/08/15 14:02:51.713
Select values
Table output
Y
Table input
Select values
Y
Select values
Insert / update
N
Insert / update
InsertUpdate
Y
1
none
100
ervu-dashboard
=
recruit_id
recruit_id
=
full_name
full_name
ervu_dashboard
recruit_id
recruit_id
N
birth_date
birth_date
Y
death_az_number
divorce_az_number
Y
death_date
death_date
Y
divorce_az_number
divorce_az_number
Y
divorce_date
divorce_date
Y
full_name
full_name
Y
information_excluded
information_excluded
Y
kinship_type
kinship_type
Y
marriage_az_number
marriage_az_number
Y
marriage_date
marriage_date
Y
N
704
208
Select values
SelectValues
Y
1
none
N
marriage_date
marriage_date
Date
-2
-2
yyyy-MM-dd
false
false
divorce_date
divorce_date
Date
-2
-2
yyyy-MM-dd
false
false
448
208
Table input
TableInput
Y
1
none
ervu_person_registry
N
0
WITH suprug AS (
SELECT
supr.supri AS supr,
ri.info->'svedFL'->'svedSemPolozh'->'svedAZBrak'->>'nomerZapis' AS marriage_az_number,
ri.info->'svedFL'->'svedSemPolozh'->'svedAZBrak'->>'dataBrakKalend' AS marriage_date,
ri.info->'svedFL'->'svedSemPolozh'->'svedAZRazvod'->>'nomerZapis' AS divorce_az_number,
ri.info->'svedFL'->'svedSemPolozh'->'svedAZRazvod'->>'dataRastBrakKalend' AS divorce_date,
CASE
WHEN (ri.info->'svedFL'->'svedSemPolozh'->>'svedSuprIskl' = '1') THEN true
ELSE false
END AS information_excluded,
ri.recruit_id
FROM public.recruits_info ri
JOIN public.recruits r ON r.id = ri.recruit_id AND (r.target_recruitment_id= '${RCRT_C}' or r.current_recruitment_id = '${RCRT_C}')
JOIN LATERAL (
SELECT jsonb_array_elements(ri.info->'svedFL'->'svedSemPolozh'->'suprugi') AS supri
WHERE jsonb_typeof(ri.info->'svedFL'->'svedSemPolozh'->'suprugi') = 'array'
) AS supr ON true
)
SELECT
r.id AS recruit_id,
supr->>'rodstvSvyazSuprugNaim' AS kinship_type,
CONCAT_WS(' ',
supr->'svedFLBS'->'fio'->>'familiya',
supr->'svedFLBS'->'fio'->>'imya',
supr->'svedFLBS'->'fio'->>'otchestvo'
) AS full_name,
MAKE_DATE(
NULLIF(supr->'svedFLBS'->'dataRozhdDok'->>'god', '')::int,
NULLIF(supr->'svedFLBS'->'dataRozhdDok'->>'mesyacz','')::int,
NULLIF(supr->'svedFLBS'->'dataRozhdDok'->>'den','')::int
) AS birth_date,
(supr->'svedSmert'->'extend'->>'dataSmert')::date AS death_date,
supr->'svedSmert'->>'nomerZapis' AS death_az_number,
marriage_az_number,
marriage_date,
divorce_az_number,
divorce_date,
information_excluded
FROM public.recruits r
JOIN suprug ON r.id = suprug.recruit_id
Y
192
208
Table output
TableOutput
Y
1
none
20000
ervu-dashboard
N
N
N
N
Y
N
ervu_dashboard
N
N
Y
N
Y
688
64
Select values
N