Last active
February 20, 2022 23:11
-
-
Save SpacePurr/edfdf4716f8958966c2d5a72ce2c0400 to your computer and use it in GitHub Desktop.
Первичные проверки
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE OR REPLACE FUNCTION minCheckData(uuid) RETURNS varchar AS $$ | |
DECLARE | |
_rec RECORD; | |
_key text; | |
_value text; | |
_jsonView json; | |
_result text := ''; | |
_nullColumns TEXT ARRAY DEFAULT ARRAY['Имя', 'Фамилия', 'Отчество', 'Пол', 'Дата рождения', 'Место рождения', | |
'Серия паспорта', 'Номер паспорта', 'Кем выдан паспорт', 'Дата выдачи паспорта', | |
'Код подразделения', 'Адрес регистрации', 'Адрес проживания']; | |
BEGIN | |
FOR _rec IN | |
SELECT | |
"GivenName" as "Имя", | |
"Surname" as "Фамилия", | |
"MiddleName" as "Отчество", | |
"GenderId" as "Пол", | |
"BirthDate" as "Дата рождения", | |
"PlaceOfBirth" as "Место рождения", | |
"Series" as "Серия паспорта", | |
"Number" as "Номер паспорта", | |
"IssuedBy" as "Кем выдан паспорт", | |
"IssueDate" as "Дата выдачи паспорта", | |
"CodeDivision" as "Код подразделения", | |
"RegistrationAddress" as "Адрес регистрации", | |
"ResidenceAddress" as "Адрес проживания", | |
"IsAllConsentReceived" as "Все согласия получены", | |
part."NsParticipantTypeId" | |
FROM "AppForm" a | |
JOIN "NsFinApplicationParticipant" part ON part."NsAppFormId" = a."Id" | |
WHERE part."NsFinApplicationId" = $1 | |
AND part."NsParticipantTypeId" IN ('3a1e13c3-c6e7-4dec-8642-de42059309cd', 'cd126efb-5e72-48ad-8fd1-d2af6231ab8c') | |
LOOP | |
IF _rec."NsParticipantTypeId" = '3a1e13c3-c6e7-4dec-8642-de42059309cd' THEN | |
_result := _result || 'Анкета заемщика:'; | |
ELSEIF _rec."NsParticipantTypeId" = 'cd126efb-5e72-48ad-8fd1-d2af6231ab8c' THEN | |
_result := _result || 'Анкета созаемщика:'; | |
END IF; | |
SELECT row_to_json(_rec) INTO _jsonView; | |
FOR _key, _value IN select * from json_each(_jsonView) | |
LOOP | |
IF _key = 'Все согласия получены' and _value = 'false' THEN | |
_result := _result || E'\n' || ' ' || _key; | |
ELSEIF _key =ANY(_nullColumns) and (_value = 'null' or _value = '""') THEN | |
_result := _result || E'\n' || ' ' || _key; | |
END IF; | |
END LOOP; | |
_result := _result || E'\n'; | |
END LOOP; | |
RETURN _result; | |
END | |
$$ language plpgsql; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment