SELECT act_list.id,
       act_list.list_number,
       act_list.list_name,
       max(f003d.nam_mok::text)                                                      AS medical_organization_name,
       max(v006d.name_type_usl::text)                                                AS med_work_type_name,
       expertise_class_decomposed.expertise_purpose::text                            AS expertise_purpose,
       expertise_class_decomposed.expertise_type::text                               AS expertise_type,
       expertise_class_decomposed.expertise_theme_type::text                         AS expertise_theme_type,
       act_list.status::text                                                         AS status,
       acts_count.cnt                                                                AS count_acts,
       concat_ws(' '::text, max(users.last_name::text), max(users.first_name::text)) AS expert_last_name,
       ''::text                                                                      AS expert_first_name,
       ''::text                                                                      AS expert_patronymic,
       act_list.date_create,
       max(f003d.region_code)                                                        AS region_code,
       act_list.user_id,
       max(theme.name::text)                                                         AS theme_name,
       max(category.name::text)                                                      AS category_name,
       act_list.date_upload,
       max(f003d.mcode::text)                                                        AS medical_organization_code,
       sum(COALESCE(sanction.sum_rem, 0.0))                                          AS retention_sum,
       act_list.zpmd_id,
       theme_category.id                                                             AS list_theme_category_id,
       zpmd_tc.id                                                                    AS zpmd_theme_category_id,
       act_list.has_cover_letter_date,
       cv.number                                                                     AS cover_letter_number,
       cv.date                                                                       AS cover_letter_date,
       act_list.date_print,
       act_list.date_send,
       act_list.date_sign,
       concat_ws(' '::text, exp.family_name, exp.first_name, exp.patronymic)         AS assigned_expert,
       exp.id                                                                        AS assigned_expert_id,
       sum(sanction.sum_fine)                                                        AS total_sum_fine,
       zpmd.podr_id,
       max(COALESCE(podr_omsk.nam_mop, podr_tula.name)::text)                        AS podr_name,
       max(COALESCE(podr_omsk.lpu_1::character varying, podr_tula.podr_id)::text)    AS podr_code,
       max(act_list.old_id::text)                                                    AS old_id
FROM expert.acts_list act_list
         JOIN expert.act act ON act.act_list_id = act_list.id
         JOIN expert.theme_category theme_category ON act_list.theme_category_id = theme_category.id
         JOIN expert.expertise_class_decomposed
              ON expertise_class_decomposed.expertise_class = theme_category.expertise_class
         JOIN zpmd.zpmd ON zpmd.id = act_list.zpmd_id
         JOIN expert.theme_category zpmd_tc ON zpmd_tc.id = zpmd.theme_category_id
         JOIN directory.f003 f003d ON f003d.mcode= act_list.medical_organization_code and f003d.region_code = '74'
         JOIN (SELECT count(1) cnt, act_list_id FROM expert.act GROUP BY act_list_id HAVING count(1) = coalesce( :countActs,count(1))) acts_count
              on acts_count.act_list_id = act_list.id
         LEFT JOIN expert.theme ON theme_category.theme_id = theme.id
         LEFT JOIN expert.category ON category.id = theme_category.category_id
         LEFT JOIN expert.expertise_result re
                   ON act.id = re.act_id AND re.status <> 'DELETED'::expert.expertise_result_status
         LEFT JOIN expert.expertise_sanction sanction ON re.id = sanction.expertise_result_id AND
                                                         (sanction.sum_fine IS NOT NULL OR sanction.sum_rem IS NOT NULL)
         LEFT JOIN directory.v006 v006d ON v006d.code_type_usl = act_list.med_work_type_id

         LEFT JOIN expert.users users ON users.id = act_list.user_id
         LEFT JOIN expert.document cv ON act_list.id = cv.act_list_id AND cv.type = 'COVER_LETTER'::expert.document_type
         LEFT JOIN directory.expert exp ON exp.id = act_list.assigned_expert_id
         LEFT JOIN directory.podr_omsk podr_omsk
                   ON act_list.region= 'OMSK' AND podr_omsk.id = zpmd.podr_id
         LEFT JOIN directory.t_podr_tula podr_tula
                   ON act_list.region = 'TULA' AND podr_tula.id = zpmd.podr_id
WHERE NOT EXISTS(SELECT 1
                 FROM bills.zsl_additional_mek mek
                 WHERE mek.zsl_id = act.zsl_id
                   AND mek.status = 'LOCKED'
                   AND (mek.region = 'CHELYABINSK'::expert.region AND act_list.region = 'CHELYABINSK'::expert.region
                     OR mek.region = 'NOVGOROD'::expert.region AND act_list.region = 'NOVGOROD'::expert.region))
GROUP BY act_list.id, act_list.list_number, act_list.list_name, (expertise_class_decomposed.expertise_purpose::text),
         (expertise_class_decomposed.expertise_type::text), (expertise_class_decomposed.expertise_theme_type::text),
         (act_list.status::text), ''::text, act_list.date_create, act_list.user_id, act_list.date_upload, acts_count.cnt,
         act_list.zpmd_id, theme_category.id, zpmd_tc.id, act_list.has_cover_letter_date, cv.number, cv.date, exp.id,
         zpmd.podr_id
order by act_list.date_create
limit 25;