WITH persons_filtered AS (
  select r.id_person, r.id_exactor, max(r.date_exactor) as date_exactor from cl_recovery r
  inner join cl_ref_employees re on r.id_exactor = re.id_exactor
  where r.date_exactor between to_date(concat('2018-01-27', ' 00-00-00'), 'YYYY-MM-DD HH24-MI-SS') and to_date(concat('2018-02-02', ' 23-59-59'), 'YYYY-MM-DD HH24-MI-SS')
  group by r.id_person, r.id_exactor
),
recovery_filtered as (
 SELECT r.*,
 case when (act_method_code='K21' and claim_at_met_start is not null)
 then least(claim_at_met_start, coalesce(sum_paid, 0))
 else coalesce(sum_paid, 0)
 end as claim_sum_paid
 FROM (
  select id_person, id_exactor, id_credit, evid_srv, date_exactor,
  act_method_code, sum_paid as sum_paid, actual_debt, claim_at_met_start, is_redistr,
  ROW_NUMBER() OVER (PARTITION BY id_person, id_exactor, id_credit ORDER BY id_package DESC) rnk
  FROM cl_recovery r
 ) r
 where r.date_exactor between to_date(concat('2018-01-27', ' 00-00-00'), 'YYYY-MM-DD HH24-MI-SS') and to_date(concat('2018-02-02', ' 23-59-59'), 'YYYY-MM-DD HH24-MI-SS')
)

SELECT
    case when to_char(trunc(pf.date_exactor),'d') = 1
        then CONCAT(trunc(pf.date_exactor), CONCAT( ' - ', trunc(pf.date_exactor) + 6))
        else CONCAT(trunc(pf.date_exactor, 'iw')-1, CONCAT( ' - ', trunc(pf.date_exactor, 'iw') + 5))
    end as registry, -- Реестр строкой

    case when to_char(trunc(pf.date_exactor),'d') = 1
        then trunc(pf.date_exactor)
        else trunc(pf.date_exactor, 'iw')-1
    end as registry_start, -- Дата начала реестра

    case when to_char(trunc(pf.date_exactor),'d') = 1
        then trunc(pf.date_exactor) +6
        else trunc(pf.date_exactor, 'iw')+5
    end as registry_end, -- Дата начала реестра

   regexp_replace(
        (case when a.tax_code is not null then a.tax_code || ', ' else '' end) ||
        (case when a.region is not null then a.region || ', ' else '' end) ||
        (case when a.district is not null then a.district || ', ' else '' end) ||
        (case when a.locality_type is not null then a.locality_type || ' ' else '' end) ||
        (case when a.town is not null then a.town || ', ' else '' end) ||
        (case when a.street_type is not null then a.street_type || ' ' else '' end) ||
        (case when a.street is not null then a.street || ', ' else '' end) ||
        (case when a.street_num is not null then 'д. ' || a.street_num || ', ' else '' end) ||
        (case when a.block_num is not null then 'корп. ' || a.block_num || ', ' else '' end) ||
        (case when a.building_num is not null then 'стр. ' || a.building_num || ', ' else '' end) ||
        (case when a.apprt_num is not null then 'кв. ' || a.apprt_num || ', ' else '' end)
      , ', $', '') as address,

    ad.name AS direction, -- Дирекция
    ar.name AS rc, -- РЦ
    rct.name AS collection_type, -- Тип сотрудника
    rcm.name AS collection_method, -- Метод взыскания
    ex_fio, -- ФИО сотрудника
    pd.pers_fio, -- ФИО заемщика
    dp.name AS priority,
 CONCAT(rf.id_exactor, CONCAT('_',rf.id_person)) as row_id,
    COALESCE(va.visits_number, 0) AS visit_number, -- Кол-во визитов
    va.last_visit_date, --дата последнего выезда
    COALESCE(ca.calls_number, 0) AS call_number, -- Кол-во звонков
    CASE WHEN COALESCE(pa.active_number, 0)>0 THEN 1 ELSE 0 END AS active_promises, --Наличие активных обещаний у Заемщика (да/нет)
    pl.plan_date, --Дата посещения в плане
    rf.claim_at_met_start as actual_debt,
    rf.claim_sum_paid as sum_payin
FROM persons_filtered pf
LEFT JOIN cl_person_data pd ON pd.id_person = pf.id_person
LEFT JOIN cl_dic_priority dp ON dp.code=pd.priority
left join cl_dic_address_type dat on dat.code='2'
left join cl_visiting_address a on a.id_person=pf.id_person and a.is_active = 1 and a.type_id = dat.id

LEFT JOIN (
    SELECT va1.id_exactor, va1.id_person,
    SUM(va1.visits_number) AS visits_number,
    MAX(va1.visit_date) AS last_visit_date
    FROM cl_visiting_analytics va1
    INNER JOIN persons_filtered pf1 ON pf1.id_person=va1.id_person AND pf1.id_exactor=va1.id_exactor AND pf1.date_exactor<=va1.visit_date
    GROUP BY va1.id_exactor, va1.id_person
) va ON va.id_exactor=pf.id_exactor AND va.id_person=pf.id_person AND va.visits_number>0
LEFT JOIN (
    SELECT va1.id_exactor, va1.id_person,
    SUM(va1.calls_number) AS calls_number
    FROM cl_visiting_analytics va1
    INNER JOIN persons_filtered pf1 ON pf1.id_person=va1.id_person AND pf1.id_exactor=va1.id_exactor AND pf1.date_exactor<=va1.visit_date
    GROUP BY va1.id_exactor, va1.id_person
) ca ON ca.id_exactor=pf.id_exactor AND ca.id_person=pf.id_person AND ca.calls_number>0
LEFT JOIN (
    SELECT pa1.id_exactor, pa1.id_person,
    SUM(pa1.active_number) AS active_number
    FROM cl_promises_analytics pa1
    GROUP BY pa1.id_exactor, pa1.id_person
) pa ON pa.id_exactor=pf.id_exactor AND pa.id_person=pf.id_person
LEFT JOIN (
  SELECT id_exactor, id_person, MIN(plan_date) AS plan_date
  FROM cl_plan_action pa
  WHERE status_id=(SELECT id FROM cl_dic_plan_action_status WHERE code=1) AND plan_date>sysdate
  GROUP BY id_exactor, id_person
) pl ON pl.id_exactor=pf.id_exactor AND pf.id_person=pl.id_person
left join (
 select id_person, id_exactor, count(id_credit) as recovery_number, max(date_exactor) as date_exactor, sum(sum_paid) as sum_paid,
 sum(actual_debt) as actual_debt, sum(claim_at_met_start) as claim_at_met_start,
 sum(claim_sum_paid) as claim_sum_paid
 from recovery_filtered
 group by id_person, id_exactor
 )rf on pd.id_person = rf.id_person and pf.id_exactor = rf.id_exactor
INNER JOIN cl_exactor ex ON ex.id_exactor = pf.id_exactor
INNER JOIN cl_ref_employees re ON re.id_exactor = pf.id_exactor
LEFT JOIN cl_acl_sid ac ON (ac.id = re.acl_sid_id)
LEFT JOIN cl_acl_sid ar ON (ar.id = ac.parent_id)
LEFT JOIN cl_acl_sid ad ON (ad.id = ar.parent_id)
LEFT JOIN cl_ref_collection_methods rcm ON rcm.id = re.collection_method_id
LEFT JOIN cl_ref_collector_type rct ON rct.id = re.collection_type_id
order by pf.date_exactor, ex_fio