---

CREATE OR REPLACE FUNCTION ourpension.get_contract_fields_main(v_contract_id integer)
RETURNS TABLE(id integer, number json, date text, investor character varying, fund_name character varying, fund_name_short character varying, service_type_rus text, invest_type_text text, contract_life_cicle character varying, sale_channel_id integer, sale_channel_text character varying, comment text, action_date text, agent_code character varying, agent_name text, previous_insurer_name character varying, new_insurer_name character varying, annulled_date text, create_date text, fund_branch_name character varying, contract_class_name character varying, name_in_pfr character varying, contract_type text, affiliate_program_contract_id integer, affiliate_program_contract_text text, family_member boolean, department_type character varying)
LANGUAGE plpgsql
AS $function$

BEGIN

-- Функция выводит поля Общей информации по договору. Выделено для облегчения запроса в модели Карточки договора.

-- Создано: 01.2026 dv.pakhomov

-- Изменения:

-- 24.03.2026 dv.pakhomov Убрал задвоение записей из-за неправильного подключения ourpension.contract_class_history

-- 24.04.2026 dv.pakhomov Получение названий фондов из справочника ourpension.fund    

    RETURN query

    WITH s AS(

        SELECT        

            c.id,

            CASE

                WHEN c.service_type = 'NPO' AND c.individual_id IS NOT NULL THEN 'ИПП'

                WHEN c.service_type = 'NPO' AND c.entity_id IS NOT NULL THEN 'КПП'

                WHEN c.service_type = 'PDS' THEN 'ДДС'

                ELSE ''

            END as contract_type,

            to_json(c.number) as number,

            c.date,

            coalesce(e.short_name, i.last_name || ' ' || i.first_name || ' ' || i.middle_name) as investor,

            coalesce(uat.activity_rus_type, 'ОПС') as service_type_rus,

            case c.invest_type

                when 1 then 'Инвестиционный'

                when 2 then 'Страховой'

            end as invest_type_text,

            c.sale_channel_id,

            c.comment,

            ha.short_name as department_type,

            ag.code as agent_code,

            coalesce(ag.last_name,'') || ' ' || left(coalesce(ag.first_name, ''), 1) || '.' || coalesce(left(ag.middle_name, 1) || '.', '') as agent_name,

            pi.previous_insurer_pfr_name previous_insurer_name,

            ni.new_insurer_pfr_name new_insurer_name,

            cch.begin_date as create_date,

            fb.name as fund_branch_name,

            cch.class_name,

            c.affiliate_program_contract_id,

            to_json( CONCAT( ce.number, ' (', ea.short_name, ')' ) )::text as affiliate_program_contract_text,

            fsg.name_full as fund_name,

            fsg.name_short as fund_name_short,

            fsr.name_in_pfr as name_in_pfr,

            c.family_member

        

        FROM ourpension.contract c        

            LEFT JOIN ourpension.individual i ON c.individual_id = i.id

            LEFT JOIN ourpension.fund fsg ON fsg.id = c.sign_fund_id    -- Заключивший фонд

            LEFT JOIN ourpension.fund fsr ON fsr.id = c.fund_id         -- Фонд-источник

            LEFT JOIN ourpension.agent ag

            ON ag.id = c.agent_id

            LEFT JOIN ourpension.fund_branch fb

            ON fb.id = ag.fund_branch_id

            LEFT JOIN ourpension.uv_activity_type uat ON c.client_type = uat.client_type AND c.service_type = uat.activity_eng_type

            LEFT JOIN ourpension.ops_get_contract_new_insurer(c.id) ni ON c.service_type = 'OPS'

            LEFT JOIN ourpension.ops_get_contract_previous_insurer(c.id) pi ON c.service_type = 'OPS'

            LEFT JOIN ourpension.contract ce

            ON ce.id = c.affiliate_program_contract_id

            LEFT JOIN ourpension.entity ea

            ON ea.id = ce.entity_id        

            LEFT JOIN ourpension.holding ha

            ON ha.id = ea.holding_id

            LEFT JOIN LATERAL

            (

                SELECT

                    cch1.begin_date,

                    cc1."name" AS class_name

                    FROM ourpension.contract_class_history cch1

                        INNER JOIN ourpension.contract_classes cc1 ON cch1.class_id = cc1.id

                    WHERE cch1.contract_id = c.id

                    ORDER BY cch1.begin_date DESC

                    LIMIT 1

                ) cch ON true

            LEFT JOIN ourpension.entity e ON e.id = c.entity_id

    

        WHERE c.id = v_contract_id        

    ),

    cte_statuses_l AS

    ( -- Последний (актуальный) статус

        SELECT

            cs1."name" AS status_name,

            CASE WHEN sh1.code IN ( 'EA0', 'JA0', 'PA0' ) THEN sh1.status_date END as annulled_date

        FROM ourpension.contract_statuses_history sh1

            INNER JOIN ourpension.contract_statuses cs1 on cs1.code = sh1.code

        WHERE sh1.contract_id = v_contract_id

            AND CURRENT_TIMESTAMP BETWEEN

                sh1.status_date AND COALESCE( sh1.status_end_date, 'infinity' )

        LIMIT 1

    ),

    cte_statuses_a AS

    ( -- Статус введения в действие

        SELECT sh.status_date

        FROM ourpension.contract_statuses_history sh        

        WHERE sh.contract_id = v_contract_id

            AND sh.code IN ( 'CA0', 'HA0', 'MA0' )

        ORDER BY sh.status_date

        LIMIT 1

    )

    SELECT

        s.id,

        s.number,

        to_char(s.date, 'dd.MM.yyyy') as "date",

        s.investor,

        s.fund_name,

        s.fund_name_short,

        s.service_type_rus,

        s.invest_type_text,

        csl.status_name as contract_life_cicle,

        s.sale_channel_id,

        sc.name as sale_channel_text,

        s.comment,

        to_char(csa.status_date, 'dd.MM.yyyy') as action_date,

        s.agent_code,

        s.agent_name,

        s.previous_insurer_name,

        s.new_insurer_name,

        to_char(csl.annulled_date, 'dd.MM.yyyy') as annulled_date,

        to_char(s.create_date, 'dd.MM.yyyy') as create_date,

        s.fund_branch_name,

        s.class_name as contract_class_name,

        s.name_in_pfr,

        s.contract_type,

        s.affiliate_program_contract_id,

        s.affiliate_program_contract_text,

        s.family_member,

        s.department_type

    FROM s

        INNER JOIN cte_statuses_l csl ON true

        LEFT JOIN ourpension.information_source sc on sc.id = s.sale_channel_id

        LEFT JOIN cte_statuses_a csa ON true;



END;

$function$