--han_PriemGosp_pg.rptdesign
--select * from rpt.han_priemgosp_pg(
with param as (
	select
		cast($1 as bigint) as p_lpu_id,
		cast($2 as bigint) as p_lpubuilding_id,
		cast($3 as bigint) as p_lpuunit_id,
		cast($4 as bigint) as p_lpusection_id,
		coalesce(dbo.GetDiagCodeByID(cast($5 as bigint)), 'A.') as p_begdiag_code,
		coalesce(dbo.GetDiagCodeByID(cast($6 as bigint)), 'Z99.9') as p_enddiag_code,
		cast(to_timestamp($7, 'dd.mm.yyyy') as timestamp) as p_begdate,
		cast(to_timestamp($8, 'dd.mm.yyyy') as timestamp) as p_enddate,
		cast($9 as bigint) as p_yesno,
		cast($10 as bigint) as p_nationality,
		cast($11 as bigint) as p_diagsetphase_id,
		cast($12 as bigint) as p_order413
)
select 
    EvnPS.DiagSetPhase_pid,
    row_number() 
        over(
            order by rtrim(coalesce(' ' || Person.Person_SurName, '')) || ' ' ||
                     rtrim(coalesce(' ' || Person.Person_FirName, '')) || 
                     rtrim(coalesce(' ' || Person.Person_SecName, ''))
        ) as Record_Num,
    EvnPS.EvnPS_id,
    EvnPS_NumCard as v2,
    to_char(EvnPS.EvnPS_setDT, 'dd.mm.yyyy') as v3,
    EvnPS.EvnPS_setTime as v4,
    LpuSection.LpuSection_Name as v5,
    rtrim(coalesce(' ' || Person.Person_SurName, '')) || ' ' || 
    rtrim(coalesce(' ' || Person.Person_FirName, '')) || 
    rtrim(coalesce(' ' || Person.Person_SecName, '')) as v6,
    to_char(Person.Person_Birthday, 'dd.mm.yyyy') as v7,
    EvnPS.Person_Age as v8,
    KLcountry_Name as v9,
    Polis.Polis_Ser as v10,
    case when Polis.PolisType_id = 4 then Person.Person_EdNum else Polis.Polis_Num end as v11,
    OrgSmo.OrgSMO_Name as v12,
    PC.Lpu_Attach as v13,                           -- Мо прикрепления последнего
    AUReg.Address_Address as v14,                   -- регистрации
    AddrFact.Address_Address as v15,                -- фактический 
    JobOrg.Org_Nick as v16,                         -- место работы
    case
        when length(ps.Person_Phone) = 10 
        then '(' || left(ps.Person_Phone, 3) || ')-' || substring(ps.Person_Phone, 4, 3) 
            || '-' || substring(ps.Person_Phone, 7, 2) || '-' || right(ps.Person_Phone, 2)
        else ''
    end as v17,                                     -- телефон
/*    case 
        when EvnPS.PrehospDirect_id in (4, 5, 6, 7) then PrehospDirect.PrehospDirect_Name
        when EvnPS.PrehospDirect_id = 1 then LpuSectioPrehosp.LpuSection_Name
        when EvnPS.PrehospDirect_id = 2 then Lpu.Lpu_name
        when EvnPS.PrehospDirect_id = 3 then Org.Org_Name 
    end as v18,*/
		case when EvnPS.PrehospDirect_id in (2,3) then Org.Org_Name
			else LpuSectioPrehosp.LpuSection_Name end as v18, --Кем направлен
		PrehospArrive.PrehospArrive_Name as v19, --Способ доставки
    d.diag_code||' '||D.diag_Name::text as v20,
    diag.diag_code||' '||Diag.diag_Name::text as v21,
    MP.Person_Fio as v22,
	VP.Name as v23,
	--MP.Dolgnost_Name as v23,
    LSGosp.LpuSection_FullName as v24,    
    PWC.PrehospWaifRefuseCause_Name as v25,    
    EvnPS_OutcomeDT as v26,
    cast(datediff('minute', EvnPS_setDT, EvnPS_OutcomeDT) / 60 as varchar) || ' ч ' 
        || cast(datediff('minute', EvnPS_setDT, EvnPS_OutcomeDT) % 60 as varchar) || ' мин' as v27,        
    DiagSetPhase.DiagSetPhase_name as v28    
from param 
inner join dbo.v_EvnPS as EvnPS on true
inner join dbo.v_LpuSection LpuSection on LpuSection.LpuSection_id = EvnPS.LpuSection_pid
  and (LpuSection.LpuSection_id = p_LpuSection_id or p_LpuSection_id is null)
  and (LpuSection.Lpu_id = p_Lpu_id or p_Lpu_id is null)
inner join dbo.v_Person_all as Person on Person.PersonEvn_id = EvnPS.PersonEvn_id 
	and Person.Server_id = EvnPS.Server_id 
/*inner join lateral (
	select ps.*
	from dbo.v_person_all ps
	where ps.person_id = evnps.person_id
	and ps.personevn_insdt <= evnps.evnps_disdate
	order by ps.personevn_insdt desc
	limit 1
) Person on true */ 
inner join dbo.v_personstate ps on ps.person_id = EvnPS.person_id
inner join dbo.LpuUnit on LpuUnit.LpuUnit_id = LpuSection.LpuUnit_id
    and (LpuUnit.LpuUnit_id = p_LpuUnit_id or p_LpuUnit_id is null)
    and (LpuUnit.LpuBuilding_id = p_LpuBuilding_id or p_LpuBuilding_id is null)
left join dbo.CovidEvnPS on CovidEvnPS.EvnPS_id = EvnPS.EvnPS_id
left join dbo.v_LpuSection LSGosp on EvnPS.LpuSection_eid = LSGosp.LpuSection_id          -- #177472#note-21
left join dbo.Diag Diag on Diag.Diag_id = EvnPS.Diag_pid
left join dbo.Address AUReg on AUReg.Address_id = Person.UAddress_id                -- регистрации
left join dbo.Address AddrFact on AddrFact.Address_id = Person.PAddress_id             -- проживания
left join dbo.Polis on Polis.Polis_id = Person.Polis_id
left join dbo.v_OrgSMO as OrgSMO on OrgSMO.OrgSMO_id = Polis.OrgSmo_id
left join dbo.PrehospDirect on EvnPS.PrehospDirect_id = PrehospDirect.PrehospDirect_id
left join dbo.PrehospArrive on EvnPS.PrehospArrive_id = PrehospArrive.PrehospArrive_id --Способ доставки

left join dbo.v_Lpu as lpu on lpu.lpu_id = EvnPS.lpu_did
left join dbo.LpuSection as LpuSectioPrehosp on EvnPS.LpuSection_did = LpuSectioPrehosp.LpuSection_id
left join dbo.v_Org as Org on Org.Org_id = EvnPS.Org_did
/*left join dbo.v_MedPersonal MP on EvnPS.MedPersonal_pid = MP.MedPersonal_id 
    and MP.Lpu_id = EvnPS.Lpu_id*/
left join dbo.v_medstafffact MP on EvnPS.medstafffact_pid = MP.medstafffact_id 
left join persis.v_post vp on MP.post_id = vp.id
left join dbo.v_PrehospWaifRefuseCause PWC on EvnPS.PrehospWaifRefuseCause_id = PWC.PrehospWaifRefuseCause_id
left join dbo.Diag D on D.Diag_id = EvnPS.Diag_did
left join dbo.v_PersonNationalityStatus PNS on PNS.Person_id = Person.Person_id
    and Person.NationalityStatus_id = PNS.NationalityStatus_id
left join dbo.v_KLcountry KLcountry on PNS.KLcountry_id = KLcountry.KLcountry_id
left join dbo.DiagSetPhase on EvnPS.DiagSetPhase_pid = DiagSetPhase.DiagSetPhase_id
left join dbo.CovidType on CovidType.CovidType_id = CovidEvnPS.CovidType_id
left join dbo.Job pjob on ps.Job_id = pjob.Job_id
left join dbo.v_Org JobOrg on JobOrg.org_id = pjob.org_id
left join lateral ( 
    select 
        L.Lpu_nick as Lpu_Attach
    from dbo.v_PersonCard_all PC 
    left join dbo.v_Lpu L 
        on L.Lpu_id = pc.Lpu_id
    where (1 = 1)        
        and coalesce(L.Lpu_IsTest, 1) = 1
        and PC.LpuAttachType_id = 1 
        and PC.Person_id = Person.Person_id
        and cast(PC.PersonCard_begDate as date) <=  p_enddate
        and (cast(PC.PersonCard_endDate as date) > p_enddate or PC.PersonCard_endDate is null)
    order by PC.PersonCard_endDate desc
    limit 1
) PC on true -- последнее прикрепление
where (1 = 1)
    and (EvnPS.Lpu_id = p_Lpu_id or p_Lpu_id is null)
		and (EvnPS.EvnPS_setDT between p_begdate + '1day'::interval * (1 - coalesce(p_Order413, 2))	+ '1hour'::interval * case when dbo.GetRegion() in (58,50) then 8 else 9 end
				and p_enddate	+ '1day'::interval * (2 - coalesce(p_Order413, 2)) + '1hour'::interval * case when dbo.GetRegion() in (58,50) then 8 else 9 end - interval '1 minute')
    --and (EvnPS.EvnPS_setdate between p_begdate and p_enddate)
    and (EvnPS.LpuSection_pid is not null)
    and (Diag.Diag_Code >= p_BegDiag_Code or p_BegDiag_Code is null)
    and (Diag.Diag_Code <= p_EndDiag_Code or p_EndDiag_Code is null)
    and (
        case 
            when EvnPS.PrehospWaifRefuseCause_id is not null and LpuSection_eid is null then 2 
            when EvnPS.PrehospWaifRefuseCause_id is null and LpuSection_eid is not null then 1 
            else null
        end = p_YesNo or p_YesNo is null
    )
    and (p_Nationality is null or p_Nationality = 1 and PNS.KLcountry_id = 643 or p_Nationality = 2 and PNS.KLcountry_id != 643) --#177472
    and (EvnPS.DiagSetPhase_pid = p_DiagSetPhase_id or p_DiagSetPhase_id is null or (p_DiagSetPhase_id = -2 and EvnPS.DiagSetPhase_pid is null))
