with lab_request as (
    select
        elruc.EvnLabRequest_id,
        elruc.UslugaComplex_id,
        elruc.EvnLabSample_id,
        elruc.EvnUslugaPar_id
    from v_EvnLabRequestUslugaComplex as elruc 
    join v_EvnLabRequest as elr 
        on elr.EvnLabRequest_id = elruc.EvnLabRequest_id
    where elruc.EvnUslugaPar_id = any(('{'||$1||'}')::int8[])
),
report_data as (
    select distinct
        LS.EvnLabSample_id as "EvnLabSample_id",
        lp.Lpu_Name as "Lpu_Name", 
        org.Org_INN as "Org_INN",
        org.Org_KPP as "Org_KPP",
        concat_ws(' ', PS.Person_SurName, PS.Person_FirName, PS.Person_SecName) as "Person_FirName",
        extract(year from age(LS.EvnLabSample_setDate, PS.Person_BirthDay)) as age,
        (select sex_name from sex where sex_id = PS.Sex_id) as sex,
        to_char(PS.Person_BirthDay, 'dd.mm.yyyy') as "Person_BirthDay",
        PC.PersonCard_Code as "PersonCard_Code",
        to_char(LS.EvnLabSample_setDt, 'dd.mm.yyyy HH24:MI') as "EvnLabSample_setDate",
        to_char(LS.evnlabsample_studydt, 'dd.mm.yyyy HH24:MI') as "EvnLabSample_StudyDt",
        LR.EvnLabRequest_BarCode as "EvnLabRequest_BarCode",
        LS.EvnLabSample_Num as "EvnLabSample_Num",
        RM.RefMaterial_Name as "RefMaterial_Name",
        ED.EvnDirection_Num as "EvnDirection_Num",
        to_char(ED.EvnDirection_setDT, 'dd.mm.yyyy hh24:mi') as "EvnDirection_setDate",
        case
        	when ed.EvnDirection_IsCito = 2 then 'да'
        	else 'нет'
        	end as cito,
        doc.Person_Fio as "Medpersonal_fio",
        lse.LpuSection_FullName as "LpuSection_Name",
        uct.UslugaComplex_id as "UslugaComplexTarget_id",
        uct.UslugaComplex_Name as "UslugaComplexTarget_Name",
        UC.UslugaComplex_id as "UslugaComplex_id",
        UC.UslugaComplex_Code as "UslugaComplex_Code",
        UC.UslugaComplex_Name as "UslugaComplex_Name",
        a_test.sort_code,
        UT.UslugaTest_ResultValue as "UslugaTest_Result",
        EUPar.EvnUslugaPar_ResultUnit as "EvnUslugaPar_ResultUnit",
        (select Unit_Name from v_Unit where EUPar.Unit_id = Unit_id) as "Unit_Name",
        EUPar.EvnUslugaPar_id as "EvnUslugaPar_id",
        UT.UslugaTest_Comment as "UslugaTest_Comment",
        UT.UslugaTest_ResultUnit as "UslugaTest_ResultUnit",
        case 
            when ut.UslugaTest_ResultQualitativeText = '' then coalesce(ut.UslugaTest_ResultLower, '') || ' - ' || coalesce(ut.UslugaTest_ResultUpper, '')   
            else coalesce(ut.UslugaTest_ResultQualitativeText, '-')
        end as limit,
        RVU.RefValuesUnit_Name as "RefValuesUnit_Name",
        AA.Analyzer_Name as "Analyzer_name",
        to_char(now(), 'dd.mm.yyyy HH24:MI') as "ReportPrintDT",
        case
            when msf.PostKind_id = 1 then msf.Person_Fio
            when msfs.PostKind_id = 6 then msfs.Person_Fio
        end as "ResponsiblePerson_FIO",
        case when coalesce(UT.Uslugatest_ResultValue, '') <> '' then rpt.try_cast(replace(UT.Uslugatest_ResultValue, ',', '.'), null::float) end as RV,
        case when coalesce(UT.Uslugatest_ResultLower, '') <> '' then rpt.try_cast(replace(UT.Uslugatest_ResultLower, ',', '.'), null::float) end as Low,
        case when coalesce(UT.Uslugatest_ResultUpper, '') <> '' then rpt.try_cast(replace(UT.Uslugatest_ResultUpper, ',', '.'), null::float) end as Up,
        case when coalesce(UT.UslugaTest_ResultLowerCrit, '') <> '' then rpt.try_cast(replace(UT.UslugaTest_ResultLowerCrit, ',', '.'), null::float) end as LCrit,
        case when coalesce(UT.UslugaTest_ResultUpperCrit, '') <> '' then rpt.try_cast(replace(UT.UslugaTest_ResultUpperCrit, ',', '.'), null::float) end as UCrit
    from lab_request as lbr
    join v_EvnLabRequest LR  
        on lbr.EvnLabRequest_id = lr.EvnLabRequest_id
    left join v_lpu lp  
        on lp.lpu_id  = LR.Lpu_id 
    left join v_org org  
        on org.org_id = lp.Org_id
    left join v_EvnDirection_all as ed 
        on ed.EvnDirection_id = lr.EvnDirection_id
    left join v_LpuSection as lse  
        on lse.LpuSection_id = ED.LpuSection_id
    left join v_EvnLabRequestUslugaComplex as elruc  
        on elruc.EvnLabRequest_id = LR.EvnLabRequest_id
    join v_EvnUslugaPar as eupar  
        on eupar.EvnUslugaPar_id = elruc.EvnUslugaPar_id
    left join v_UslugaComplex as uct 
        on uct.UslugaComplex_id = eupar.UslugaComplex_id
    -- Проба
    left join v_EvnLabSample LS on LS.EvnLabSample_id =  elruc.EvnLabSample_id
    left join lis.v_Analyzer as AA on AA.Analyzer_id = LS.Analyzer_id

    left join v_PersonState PS  
        on PS.Person_id = LS.Person_id
    left join v_PersonCard PC  
        on PS.person_id = PC.Person_id
    left join v_RefSample RS  
        on RS.RefSample_id = LS.RefSample_id
    left join v_RefMaterial RM  
        on RS.RefMaterial_id = RM.RefMaterial_id
    -- Услуги
    left join v_UslugaTest UT  
        on UT.UslugaTest_pid = eupar.EvnUslugaPar_id
    left join v_UslugaComplex UC  
        on UT.UslugaComplex_id = UC.UslugaComplex_id
    left join v_RefValues RV  
        on UC.RefValues_id = RV.RefValues_id
    left join v_RefValuesUnit RVU  
        on RV.RefValuesUnit_id = RVU.RefValuesUnit_id
    left join v_MedStaffFact as doc 
        on doc.MedStaffFact_id = ed.MedStaffFact_id
    left join v_MedStaffFact as msf 
        on msf.MedStaffFact_id = eupar.MedStaffFact_id
    left join v_MedStaffFact as msfs 
        on msfs.MedStaffFact_id = eupar.MedStaffFact_sid
    left join lateral (
        select lbrt.UslugaComplex_id
        from lab_request as lbrt 
        where coalesce(lbrt.EvnLabSample_id, LS.EvnLabSample_id) = LS.EvnLabSample_id
            and coalesce(lbrt.EvnUslugaPar_id, eupar.EvnUslugaPar_id) = eupar.EvnUslugaPar_id
            and lbrt.UslugaComplex_id = uc.UslugaComplex_id
        limit 1
    ) elruc_ on true
    
    left join lateral(
    	select AnalyzerTest_SortCode as sort_code
    	from lis.v_AnalyzerTest as at
    		inner join v_UslugaComplexMedService as ucms
    			on ucms.UslugaComplexMedService_id = at.UslugaComplexMedService_id
    	where at.Analyzer_id = AA.Analyzer_id
    		and ucms.UslugaComplex_id = uc.UslugaComplex_id
    		and coalesce(at.AnalyzerTest_IsNotActive, 1) = 1
            and coalesce(at.AnalyzerTest_endDT,date'infinity') >= LS.EvnLabSample_setDt
           limit 1
    )a_test on true
)
select 
    rd."EvnLabSample_id", 
    rd."Lpu_Name",    
    rd."Org_INN", 
    rd."Org_KPP", 
    rd."Person_FirName",
    rd.age,
    rd.sex,
    rd."Person_BirthDay",
    rd."PersonCard_Code",
    rd."EvnLabSample_setDate",
    rd."EvnLabSample_StudyDt",
    rd."EvnLabRequest_BarCode",   
    rd."EvnLabSample_Num",
    rd."RefMaterial_Name",
    rd."EvnDirection_Num",
    rd."EvnDirection_setDate",
    rd.cito,
    rd."Medpersonal_fio",
    rd."LpuSection_Name",
    rd."UslugaComplexTarget_id",
    rd."UslugaComplexTarget_Name",
    rd."UslugaComplex_id",
    rd."UslugaComplex_Code",
    rd."UslugaComplex_Name",
    rd.sort_code,
    rd."UslugaTest_Result",
    rd."EvnUslugaPar_ResultUnit",
    rd."Unit_Name",
    rd."EvnUslugaPar_id",
    rd."UslugaTest_Comment",
    rd.limit,
    rd."RefValuesUnit_Name",
    rd."Analyzer_name",
    rd."ReportPrintDT",
    rd."ResponsiblePerson_FIO",
    rd."UslugaTest_ResultUnit",
    case 
        when rd.RV > rd.UCrit and rd.Ucrit is not null then repeat(chr(9650), 2)    -- 2 стрелки вверх
        when rd.RV > rd.Up and rd.Up is not null then chr(9650)                     -- 1 стрелка вверх
        when rd.RV < rd.LCrit and rd.LCrit is not null then repeat(chr(9660), 2)    -- 2 стрелки вниз 
        when rd.RV < rd.Low and rd.Low is not null then chr(9660)                   -- 1 стрелка вниз
    end as "Arrows"
from report_data as rd
where 1 = 1
    and rd."UslugaComplexTarget_id" is not null
    and rd."UslugaTest_Result" is not null
order by
    rd."EvnLabSample_id", 
    rd."UslugaComplexTarget_Name",
    rd.sort_code
