---

WITH period AS (
    SELECT
        coalesce(
            nullif(btrim(CAST(:start_date_app AS text)), '')::date,
            current_date - 1
        ) AS date_from,
        coalesce(
            nullif(btrim(CAST(:end_date_app AS text)), '')::date,
            current_date
        ) AS date_to
),
q AS (
    SELECT DISTINCT ON (c.number)
        atc.id AS id,
        c.id AS contract_id,
        i.id AS fl_id
    FROM ourpension.contract c
    LEFT JOIN ourpension.individual i
        ON i.id = c.individual_id
    JOIN ourpension.sharer sh
        ON c.id = sh.contract_id
    LEFT JOIN ourpension.application_termination_contract atc
        ON atc.sharer_id = sh.id
    LEFT JOIN ourpension.uv_activity_type uat
        ON uat.activity_eng_type = c.service_type
       AND uat.client_type = c.client_type
    CROSS JOIN period p
    WHERE atc.date >= p.date_from
      AND atc.date < p.date_to + 1
      AND i.first_name IS NOT NULL
      AND (
          coalesce(CAST(:isChecked AS boolean), true) = false
          OR NOT EXISTS (
              SELECT 1
              FROM ourpension.year_tax_deduction td
              WHERE td.application_termination_contract_id = atc.id
                AND td.type IN ('RECEIVED', 'NOT_RECEIVED')
          )
      )
      AND (
          :contract_activity_type IS NULL
          OR :contract_activity_type = ''
          OR :contract_activity_type = 'ALL'
          OR uat.activity_rus_type = :contract_activity_type
      )
    ORDER BY c.number, atc.date DESC, atc.id DESC
)
SELECT jsonb_build_object(
    'status', EXISTS (SELECT 1 FROM q),
    'start_date_app', p.date_from::text,
    'end_date_app', p.date_to::text,
    'agent_id', (
        SELECT css.agent_id
        FROM report.conf_scheduler_sfr css
        WHERE css.id = 40
    ),
    'list', coalesce(
        (
            SELECT jsonb_agg(
                jsonb_build_object(
                    'start_b', q.fl_id,
                    'end_b', q.contract_id
                )
                ORDER BY q.contract_id, q.fl_id
            )
            FROM q
        ),
        '[]'::jsonb
    )
)::text AS payload
FROM period p;



#set($t = $db.findOne($params))
$t.payload