Полные автоматические проверки

fullCheckData.sql

CREATE OR REPLACE FUNCTION fullCheckData(uuid) RETURNS varchar AS $$
DECLARE
  _rec RECORD;
  _key text;
  _value text;
  _jsonView json;
  _result text := '';
  _nullColumns TEXT ARRAY  DEFAULT  ARRAY['Имя', 'Фамилия', 'Отчество', 'Пол', 'Дата рождения', 'Место рождения', 
                          'Серия паспорта', 'Номер паспорта', 'Кем выдан паспорт', 'Дата выдачи паспорта',
                          'Код подразделения', 'Адрес регистрации', 'Адрес проживания', 'Мобильный телефон', 'Социальный статус'];

  _notWorkColumns TEXT ARRAY  DEFAULT  ARRAY['Домашний телефон', 'Рабочий адрес'];

  _ownBusinessColumns TEXT ARRAY  DEFAULT  ARRAY['ИНН работодателя', 'Дата начала работы в компании', 'Рабочий адрес', 'Рабочий телефон'];

  _employedColumns TEXT ARRAY  DEFAULT  ARRAY['Должность', 'Дата начала работы в компании', 'Рабочий адрес', 'Наименование работодателя'];
  
  _notWorkResult text := '';
  _ownBusinessResult text := '';
  _employedResult text := '';
  _socialStatus text := '';

  _incomeId uuid;
  _mainContactId uuid;
  _clientOfferId uuid;
  _emptyVinFavoritesOffer RECORD;

  _appFormFile uuid;
  _secondDocFile uuid;
  _mainDocFile uuid;
BEGIN
  FOR _rec IN
    SELECT 
        a."Id",
        a."GivenName" as "Имя",  
        a."Surname" as "Фамилия",
        a."MiddleName" as "Отчество",
        a."GenderId" as "Пол",
        a."BirthDate" as "Дата рождения",
        a."PlaceOfBirth" as "Место рождения",
        a."Series" as "Серия паспорта",
        a."Number" as "Номер паспорта",
        a."IssuedBy" as "Кем выдан паспорт",
        a."IssueDate" as "Дата выдачи паспорта",
        a."CodeDivision" as "Код подразделения",
        a."RegistrationAddress" as "Адрес регистрации",
        a."ResidenceAddress" as "Адрес проживания",
        a."IsAllConsentReceived" as "Все согласия получены",
        a."MobilePhone" as "Мобильный телефон",
        a."NsSocialStatusId" as "Социальный статус",
        a."NsJobTitleId" as "Должность",
        a."NsWorkInCompanyStartingDate" as "Дата начала работы в компании",
        a."NsAddress" as "Рабочий адрес",
        a."NsEmployerNaming" as "Наименование работодателя",
        a."NsINN" as "ИНН работодателя",
        a."NsWorkPhone" as "Рабочий телефон",
        a."NsHomePhone" as "Домашний телефон",
        part."NsParticipantTypeId"
      FROM "AppForm" a
      JOIN "NsFinApplicationParticipant" part ON part."NsAppFormId" = a."Id"
      JOIN "FinApplication" fin ON fin."Id" = part."NsFinApplicationId"
      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;
  
        ELSEIF _key = 'Социальный статус' THEN
          _socialStatus :=  _value;
  
        ELSEIF _key =ANY(_notWorkColumns) and (_value = 'null' or _value = '""') THEN
          _notWorkResult :=  _notWorkResult || E'\n' || '     ' || _key;
  
        ELSEIF _key =ANY(_ownBusinessColumns) and (_value = 'null' or _value = '""') THEN
          _ownBusinessResult :=  _ownBusinessResult || E'\n' || '     ' || _key;
  
        ELSEIF _key =ANY(_employedColumns) and (_value = 'null' or _value = '""') THEN
          _employedResult :=  _employedResult || E'\n' || '     ' || _key;
        END IF;
      END LOOP;

    --проверка социального статуса

      IF _socialStatus = '"b5c3087a-5b68-4c44-8ca5-3555bd871ae0"' THEN --не работает
        IF _notWorkResult != '' THEN
          _result :=  _result || E'\n' || '     ' || _notWorkResult;
        END IF;
      ELSEIF _socialStatus = '"b763f002-0c60-4f19-84d3-4573f3590504"' THEN --свой бизнес
        IF _ownBusinessResult != '' THEN
          _result :=  _result || E'\n' || '     ' || _ownBusinessResult;
        END IF;
      ELSEIF _socialStatus = '"1749420d-1f95-4756-a5cd-cbb4da0eb517"' THEN --работа по найму
        IF _employedResult != '' THEN
          _result :=  _result || E'\n' || '     ' || _employedResult;
        END IF;
      END IF;

    --Доходы
      _result := _result || E'\n';
  
      SELECT "Id" 
      INTO _incomeId 
      FROM "IncomeAndExpenseInAppForm" 
      WHERE "AppFormId" = _rec."Id"
      AND "Amount" is not null
      AND "CategoryId" = '5be001a7-f71b-44df-9394-cea146a0abdf'
      LIMIT 1;
    
      IF _incomeId is null THEN
        _result :=  _result || E'\n' || '     В анкете отсутствует Доход: Тип = Основной, Сумма = заполнено';
      END IF;

    --Контактные лица
      _result := _result || E'\n';

      SELECT "Id" 
      INTO _mainContactId 
      FROM "AppFormMainContact" 
      WHERE "AppFormId" = _rec."Id"
      AND "RelationTypeId" is not null
      AND "Surname" is not null
      AND "GivenName" is not null
      AND "Birthday" is not null
      AND "ContactNumber" is not null
      LIMIT 1;
      
      IF _mainContactId is null THEN
        _result :=  _result || E'\n' || '     В анкете отсутствует Контактное лицо: Фамилия, Имя, Контактный телефон, Дата рождения = заполнено';
      END IF;

    --Файлы
      _result := _result || E'\n';
    
      SELECT "Id" INTO _mainDocFile
      FROM "AppFormFile"
      WHERE "AppFormId" = _rec."Id"
      AND "NsTypeId" = '80ec3b10-7ab7-42e2-a73d-15b71d5d8295'
      LIMIT 1;

      IF _mainDocFile is null THEN
        _result :=  _result || E'\n' || '     Отсутствует документ в анкете с типом Документ удостоверяющий личность';
      END IF;
  
      SELECT "Id" INTO _secondDocFile
      FROM "AppFormFile"
      WHERE "AppFormId" = _rec."Id"
      AND "NsTypeId" = 'ecedea23-3679-4580-85f9-7c1581503303'
      LIMIT 1;

      IF _secondDocFile is null THEN
        _result :=  _result || E'\n' || '     Отсутствует документ в анкете с типом Второй документ';
      END IF;
  
      SELECT "Id" INTO _appFormFile
      FROM "AppFormFile"
      WHERE "AppFormId" = _rec."Id"
      AND "NsTypeId" = 'ae847f28-8455-4dc4-96c9-da40e097ad94'
      LIMIT 1;

      IF _appFormFile is null THEN
        _result :=  _result || E'\n' || '     Отсутствует документ в анкете с типом Анкета клиента';
      END IF;
    
    _result := _result || E'\n';

    _socialStatus := '';
    _employedResult := '';
    _ownBusinessResult := '';
    _notWorkResult := '';
  END LOOP;

  --Наличие предложения в избранном
      _result := _result || E'\n';

      SELECT offer."Id" INTO _clientOfferId from "NsClientOffer" offer
      where offer."CalculateId" = $1 and offer."Favorites" = true
      LIMIT 1;

      IF _clientOfferId is null THEN
         _result :=  _result || E'\n' || 'Отсутствует предложение в избранном';
      END IF;

    --Наличие VIN у автомобилей с пробегом и мультибренд
    
      FOR _emptyVinFavoritesOffer IN 
        SELECT offer."Number"
        FROM "NsClientOffer" offer
        JOIN "NsCar" car ON car."Id" = offer."CarId"
        WHERE offer."CalculateId" = $1
        AND car."UsedCarId" IN ('b70aa1f8-0267-47b1-8bcf-fbe8066171fe', 'f149b728-26ab-453e-a296-766defa46fb8')
        AND car."VIN" = ''
        AND offer."Favorites" = true
      
      LOOP
          _result :=  _result || E'\n' || 'Не заполнен VIN у автомобиля в предложении номер ' || _emptyVinFavoritesOffer."Number";
      END LOOP;

  RETURN _result;
END
$$ language plpgsql;