                WITH emp_org AS (SELECT uo.org_id
                FROM pim_employee_position ep
                JOIN pim_employee e ON e.id = ep.employee_id
                JOIN sec_user_party up ON up.party_id = e.individual_id
                JOIN sec_user_org uo ON uo.user_id = up.id
                WHERE ep.id = null)

                SELECT mr.id, mrrt.name, 
                referral_type_verbose(mr.id)
            , ms.case_id, ms.id, mr.order_number, to_char(mr.referral_date, 'DD.MM.YYYY'), mr.referral_date, case when mr.status_id = 4 then true else false end, mr.ref_organization_id, coalesce(ref_org.short_name, ref_org.full_name), coalesce(ref_org.short_name, ref_org.full_name), mr.ref_department_id, ref_dep.name, mr.ref_doctor_id, po.id, po.full_name, coalesce(po.short_name, po.full_name), pd.id, pd.name, mr.recv_doctor_id, (select pi1.surname ||' '||left(initcap(pi1.name),1)||'. '||left(initcap(pi1.patr_name),1) from pim_employee_position pep1
                left join pim_employee pe1 on ( pep1.employee_id = pe1.id  )
                left join pim_individual pi1 on ( pe1.individual_id = pi1.id  )
                where pep1.id = mr.recv_doctor_id )
            , md.id, md.code || ' ' || md.name, coalesce(mr.is_urgent, false), mr.is_operation_required, 
                CONCAT(
                    (SELECT
                        (CASE
                            WHEN exists
                                (SELECT 1
                                    FROM md_referral_service mrsx
                                    WHERE mrsx.referral_id = mr.id AND main_complex IS TRUE) IS TRUE
                            THEN
                                (SELECT concat('[+] ', ss.name)
                                    FROM md_referral_service mrs1
                                    JOIN sr_service ss ON (mrs1.service_id = ss.id)
                                    WHERE mrs1.referral_id = mr.id AND main_complex IS TRUE
                                    LIMIT 1)
                                    || '; ' ||
                                (SELECT array_to_string(array(SELECT ss.name
                                    FROM md_referral_service mrs1
                                    JOIN sr_service ss ON (mrs1.service_id = ss.id)
                                    WHERE mrs1.referral_id = mr.id AND mrs1.complex_id IS NULL AND main_complex IS NOT TRUE),
                                    '; '))
                            ELSE
                                (SELECT array_to_string(array(SELECT ss.name
                                    FROM md_referral_service mrs1
                                    JOIN sr_service ss ON (mrs1.service_id = ss.id)
                                    WHERE mrs1.referral_id = mr.id AND mrs1.complex_id IS NULL AND
                                        main_complex IS NOT TRUE), '; ')
                                )
                        END)
                    ),

                    (SELECT string_agg(ssp.name, ', ')
                        FROM md_referral_service mrs
                        LEFT JOIN sr_srv_prototype ssp ON ( mrs.service_prototype_id = ssp.id )
                        WHERE mrs.referral_id = mr.id and mrs.service_prototype_id is not null
                    )
                )
            , 
                (SELECT
                    (CASE
                        WHEN exists
                            (SELECT 1
                                FROM md_referral_service mrsx
                                WHERE mrsx.referral_id = mr.id AND main_complex IS TRUE)
                        THEN
                            (SELECT ss.id
                                FROM md_referral_service mrs1
                                JOIN sr_service ss ON (mrs1.service_id = ss.id)
                                WHERE mrs1.referral_id = mr.id AND main_complex IS TRUE
                                LIMIT 1)
                        ELSE
                            (SELECT ss.id
                                FROM md_referral_service mrs1
                                JOIN sr_service ss ON (mrs1.service_id = ss.id)
                                WHERE mrs1.referral_id = mr.id AND mrs1.complex_id IS NULL AND
                                    main_complex IS NOT TRUE
                                    LIMIT 1)
                    END)
                )
            , mr.patient_id, mr.patient_id, mr.referral_type_id, 
                case when (select is_laboratory from md_referral_type where id = mr.referral_type_id) = true then true
                else false end
            , 
                case when (select hsp_rsv_mark from md_referral_type where id = mr.referral_type_id) = true then true
                else false end
            , (select ppr.id from pim_party_role ppr where ppr.code = 'CLINIC_ORGANIZATION'), ffst.id, 
                (select array_to_json(array_agg(r)) from (
                select distinct
                ss1.id as id,
                (case
                when (exists(select 1 from md_referral_service xmrs
                where xmrs.referral_id = mr.id AND xmrs.main_complex = true)) is true
                then
                concat(ss1.code,' ', ss1.name)
                else concat(ss1.code,' ', ss1.name)
                end) as name
                from md_referral_service mrs
                left join sr_service ss1 on ( mrs.service_id = ss1.id )
                where mrs.main_complex is not true and mrs.referral_id = mr.id and mrs.service_id is not null and mrs.complex_id is null) as r)
            , 
                (select array_to_json(array_agg(r)) from (
                select distinct
                ss1.id as id,
                (case
                when (exists(select 1 from md_referral_service xmrs
                where xmrs.referral_id = mr.id AND xmrs.main_complex = true)) is true
                then
                (case when sc.is_required is true then concat('[!] ',ss1.code,' ', ss1.name) else concat(ss1.code,' ', ss1.name)
                end )
                else concat(ss1.code,' ', ss1.name)
                end) as name
                from md_referral_service mrs
                left join sr_service ss1 on ( mrs.service_id = ss1.id )
                left join sr_srv_composition sc on mrs.service_id = sc.service_id and sc.complex_id = (select xmrs.service_id
                from md_referral_service xmrs
                where xmrs.referral_id = mr.id AND xmrs.main_complex = true limit 1)
                where mrs.main_complex is not true and mrs.referral_id = mr.id and mrs.service_id is not null
                and (mrs.complex_id is not null or mrs.main_complex is true)) as r)
            , 
                (select row_to_json(r) from(
                select
                ss1.id as id,
                (concat('[+] ',ss1.code,' ', ss1.name)) as name,
                (
                select array_agg(s1.id) from sr_service s1
                join sr_srv_composition sc1 on s1.id = sc1.service_id
                where sc1.complex_id = ss1.id and sc1.is_required = true
                ) as "requiredFieldsIds"
                from md_referral_service mrs
                left join sr_service ss1 on ( mrs.service_id = ss1.id )
                where mrs.main_complex is true and mrs.referral_id = mr.id
                order by mrs.id limit 1)r)
            , 
                (select array_to_json(array_agg(r)) from (
                select
                mrs.id as id,
                mrs.id as referral_service_id,
                mrs.service_prototype_id as prototype_id,
                ssp.name as prototype_name
                from md_referral_service mrs
                left join sr_srv_prototype ssp on ( mrs.service_prototype_id = ssp.id )
                where mrs.referral_id = mr.id and mrs.service_prototype_id is not null
                order by mrs.id) as r)
            , mr.comment, (
                      select array_to_json(array_agg(ar))
                        from (
                               select mra.id as id,
                                      mra.id as appendix_id,
                                      mrat.id as type_id,
                                      mrat.name as type_name,
                                      mra.appendix_name as appendix_name,
                                      mra.file_name as appendix_file_name,
                                      mra.comment as appendix_comment,
                                      (
                                        select to_json(fs)
                                          from (
                                                 select fs.url as url,
                                                        fs.path as path,
                                                        fs.original_file_name as filename
                                                   from file_storage fs
                                                  where fs.url = mra.fs_url
                                               ) as fs
                                      ) as doc
                                 from md_referral_appendix mra
                            left join md_referral_appendix_type mrat on mra.appendix_type_id = mrat.id
                                where mra.referral_id = mr.id
                             order by mra.id
                            ) as ar
                      )
            , (
                            select array_to_json(array_agg(ar))
                              from (
                                     select mra.id as id
                                       from md_referral_appendix mra
                                      where mra.referral_id = mr.id
                                   order by mra.id
                                   ) as ar
                        )
            , (select array_to_json(array_agg(r)) from (
                select
                mrs.id as id
                from md_referral_service mrs
                where mrs.referral_id = mr.id
                and (mrs.main_complex is true or mrs.complex_id is not null)
                order by mrs.id) as r)
            , (select array_to_json(array_agg(r)) from (
                select
                mrs.id as id
                from md_referral_service mrs
                where mrs.referral_id = mr.id
                and mrs.main_complex is not true and mrs.complex_id is null
                order by mrs.id) as r)
            , $1, null, 
                exists(select 1
                from md_referral_service xmrs
                where xmrs.referral_id = mr.id AND xmrs.main_complex = true)
            , 
                (case when $2 != po.id
                        then (case when ((select count(service_prototype_id) from md_referral_service
                                            where referral_id = mr.id and service_prototype_id is not null) != 1
                                        or (select count(distinct ss1.id) from md_referral_service mrs
                                                left join sr_service ss1 on ( mrs.service_id = ss1.id )
                                                where mrs.main_complex is not true and mrs.referral_id = mr.id and mrs.service_id is not null) != 1)
                                    then null
                                    else (case when (select count(id) from sr_service where org_id = po.id
                                                        and prototype_id = (select service_prototype_id from md_referral_service
                                                        where referral_id = mr.id and service_prototype_id is not null limit 1)) = 1
                                                then (select id from sr_service where org_id = po.id
                                                        and prototype_id = (select service_prototype_id from md_referral_service
                                                        where referral_id = mr.id and service_prototype_id is not null)limit 1)
                                                else null end)
                                     end)
                        else (case when (select ss1.id from md_referral_service mrs
                                            left join sr_service ss1 on ( mrs.service_id = ss1.id )
                                            where mrs.main_complex is true and mrs.referral_id = mr.id
                                            order by mrs.id limit 1) is not null
                                    then (case when (select distinct ss1.id from md_referral_service mrs
                                                        left join sr_service ss1 on ( mrs.service_id = ss1.id )
                                                        where mrs.main_complex is not true and mrs.referral_id = mr.id and mrs.service_id is not null limit 1) is not null
                                                then null
                                                else (select ss1.id from md_referral_service mrs
                                                        left join sr_service ss1 on ( mrs.service_id = ss1.id )
                                                        where mrs.main_complex is true and mrs.referral_id = mr.id
                                                        order by mrs.id limit 1)
                                                end)
                                    else (case when (select count(distinct ss1.id) from md_referral_service mrs
                                                        left join sr_service ss1 on ( mrs.service_id = ss1.id )
                                                        where mrs.main_complex is not true and mrs.referral_id = mr.id and mrs.service_id is not null) != 1
                                                then null
                                                else (select distinct ss1.id from md_referral_service mrs
                                                        left join sr_service ss1 on ( mrs.service_id = ss1.id )
                                                        where mrs.main_complex is not true and mrs.referral_id = mr.id and mrs.service_id is not null limit 1)
                                                end)
                                    end)
                        end)
            , null, null, $3, mr.receiving_speciality_id, null, mr.ref_status_id is null, 
                (select case when sv.value is not null and sv.value != '' then sv.value::boolean else s.default_value::boolean end
                from cmn_setting s
                left join cmn_setting_value sv on sv.setting_id = s.id
                where s.code='cz.atria.medcase.ui.web.ReferralUISettings.selectingServicePrototype' limit 1)
            
                FROM md_referral mr
                LEFT JOIN mc_step ms ON mr.step_id = ms.id
                LEFT JOIN pim_organization ref_org ON ref_org.id = mr.ref_organization_id
                LEFT JOIN pim_department ref_dep ON ref_dep.id = mr.ref_department_id
                LEFT JOIN md_report_referral_type mrrt ON mrrt.referral_type_id = mr.referral_type_id
                LEFT JOIN pim_organization po ON (mr.recv_organization_id = po.id)
                LEFT JOIN pim_department pd ON (mr.department_id = pd.id)
                LEFT JOIN md_diagnosis md ON (mr.diagnosis_id = md.id)
                LEFT JOIN sr_srv_rendered ssr ON (mr.service_id = ssr.id)
                LEFT JOIN sr_service ss ON (ssr.service_id = ss.id)
                LEFT JOIN fin_funding_source_type ffst ON (mr.funding_id = ffst.id)
                LEFT JOIN md_referral_type mrt ON (mr.referral_type_id = mrt.id)
                LEFT JOIN md_ref_document_status rds ON rds.id = mr.status_id
                WHERE $4=$5 and $6=$7
                ORDER BY 
                referral_type_verbose(mr.id)
             asc
             limit 6
