--Разработка на основе select * from rpt2.pan_evnpl_patientsrecordedlist_pg(cast(? as int8),cast(? as int8),cast(? as int8),cast(? as int8),cast(? as int8),cast(to_timestamp(?,'dd.mm.yyyy') as timestamp),cast(to_timestamp(?,'dd.mm.yyyy') as timestamp),cast(? as int8),cast(? as int8),?,?,?)order by v1, v6
with prm as (
select $1::bigint as lpu_id,
       $2::bigint as lpubuilding_id,
       $3::bigint as lpuunit_id,
       $4::bigint as lpusection_id,
       $5::bigint as medpersonal_id,
       to_date($6,'dd.mm.yyyy') as begdate,
       to_date($7,'dd.mm.yyyy') as enddate,
       $8::integer as terr,
       $9::bigint as lpu_did,       
	   $10::varchar as surname,
       $11::varchar as firname,
       $12::varchar as secname) 

, TTGHist as (       
select row_number() over(partition by TTGHist.TimeTableGraf_id order by TTGHist.TimeTableGraf_id, TTGHist.TimeTableGrafHist_insDT desc) num,
       TTGHist.TimeTableGrafHist_id,
       TTGHist.TimeTableGraf_id,
       cast (TTGHist.TimeTableGrafHist_insDT as date) TimeTableGrafHist_insDT,
       TTGHist.Person_id,
       TTGHist.TimetableGrafHist_userID,
       TTGHist.MedStaffFact_id,
       LpuSection.LpuSection_id,
       LpuSection.LpuSection_Name,
       MSF.Person_Fio,
       MSF.Lpu_id MSF_Lpu_id,
       prm.terr,
       prm.lpu_did,       
	   prm.surname,
       prm.firname,
       prm.secname
  from prm 
  join v_MedStaffFact MSF on (MSF.Lpu_id = prm.Lpu_id or prm.Lpu_id is null) 
    and (MSF.LpuBuilding_id = prm.LpuBuilding_id or prm.LpuBuilding_id is null) 
    and (MSF.LpuUnit_id = prm.LpuUnit_id or prm.LpuUnit_id is null) 
    and (MSF.LpuSection_id = prm.LpuSection_id or prm.LpuSection_id is null) 
    and (MSF.MedPersonal_id = prm.MedPersonal_id or prm.MedPersonal_id is null)
  join TimeTableGrafHist TTGHist on MSF.MedStaffFact_id = TTGHist.MedStaffFact_id 
  left join v_LpuSection LpuSection on LpuSection.LpuSection_id = MSF.LpuSection_id                  
where TTGHist.TimeTableGrafAction_id = 2 and TTGHist.TimeTableGrafHist_insDT::date  between prm.begdate and prm.enddate) 

select COALESCE(ltrim(PS.Person_SurName) || ' ', '') || COALESCE(ltrim(PS.Person_FirName) || ' ', '') || COALESCE(ltrim(PS.Person_SecName), '') v1,
       PS.Person_BirthDay v2,
       UPAddress.Address_Address v3,
       PersonCardOsn.Lpu_Nick v4,
       PersonCardSl.Lpu_Nick v5,
       TTGHist.TimeTableGrafHist_insDT v6,
       LpuUser.Lpu_Nick v7,
       dateadd('day', - 1, cast (v_Day.day_date as date)) v8,
       TTGHist.LpuSection_Name v9,
       TTGHist.Person_Fio v10,
       EvnPL_fact.EvnPL_setDate v11,
       EvnPL_fact.LpuSection_Name v12,
       EvnPL_fact.Person_Fio v13        
  from TTGHist 
  join v_TimeTableGraf TTG on TTGHist.TimeTableGraf_id = TTG.TimeTableGraf_id and TTGHist.num = 1
  join v_PersonState PS on PS.Person_id = TTGHist.Person_id 
    and ((lower(TTGHist.surname) != 'null' and ltrim(PS.Person_SurName) iLIKE TTGHist.surname || '%') or (TTGHist.surname is null) or (lower(TTGHist.surname) = 'null')) 
    and ((lower(TTGHist.firname) != 'null' and ltrim(PS.Person_FirName) iLIKE TTGHist.firname || '%') or (TTGHist.firname is null) or (lower(TTGHist.firname) = 'null')) 
    and ((lower(TTGHist.secname) != 'null' and ltrim(PS.Person_SecName) iLIKE TTGHist.secname || '%') or (TTGHist.secname is null) or (lower(TTGHist.secname) = 'null')) 
  join v_Day on v_Day.day_id = TTG.TimeTableGraf_Day
  left join v_Address as UPAddress on UPAddress.Address_id = COALESCE(PS.UAddress_id, PS.PAddress_id)
  left join v_pmUserCache usr on TTGHist.TimetableGrafHist_userID = usr.PMUser_id
  left join v_Lpu LpuUser on LpuUser.Lpu_id = usr.Lpu_id
  left join v_Address Address on Address.Address_id = COALESCE(LpuUser.UAddress_id, LpuUser.PAddress_id)
  left join v_KLCity KLCity on KLCity.KLCity_id = Address.KLCity_id and KLCity.KLArea_pid = 2 and KLCity.KLSocr_Nick = 'Г' --город РБ
  left join v_KLAreaStat KLAreaStat on COALESCE(KLAreaStat.KLSubRGN_id, KLAreaStat.KLCity_id) = COALESCE(Address.KLSubRGN_id, Address.KLCity_id)
  left join lateral (
       select Lpu.Lpu_Nick
         from v_PersonCard_all PC
         join v_Lpu Lpu on Lpu.Lpu_id = PC.Lpu_id
       where TTGHist.Person_id = PC.Person_id and PC.LpuAttachType_id = 1 order by PC.PersonCard_begDate desc limit 1) PersonCardOsn on true
  left join lateral  (
       select Lpu.Lpu_Nick
         from v_PersonCard_all PC
         join v_Lpu Lpu on Lpu.Lpu_id = PC.Lpu_id
       where TTGHist.Person_id = PC.Person_id and PC.LpuAttachType_id = 4 order by PC.PersonCard_begDate desc limit 1) PersonCardSl on true
  left join lateral (
       select cast (EvnPL.EvnPL_setDate as date) EvnPL_setDate,
              LS.LpuSection_Name ,
              MedPersonal.Person_fio
         from v_EvnPL EvnPL
         join v_LpuSection LS on LS.LpuSection_id = EvnPL.LpuSection_id                  
         left join lateral (select MedPersonal.Person_fio from v_MedPersonal MedPersonal where MedPersonal.MedPersonal_id = EvnPL.MedPersonal_id limit 1) MedPersonal on true
       where EvnPL.Lpu_id = TTGHist.MSF_Lpu_id and
             EvnPL.Person_id = TTGHist.Person_id and
             EvnPL.EvnPL_setDT >= TTGHist.TimeTableGrafHist_insDT and
             cast (EvnPL.EvnPL_setDate as date) <= dateadd('day', 15, TTGHist.TimeTableGrafHist_insDT) and
             LS.LpuSection_id = TTGHist.LpuSection_id order by EvnPL.EvnPL_setDT limit 1) EvnPL_fact on true
where ((TTG.Person_id is not null) or (TTG.Person_id is null and EvnPL_fact.EvnPL_setDate is not null)) and
      (KLAreaStat.KLAreaStat_Code = TTGHist.terr or TTGHist.terr is null) and
      (LpuUser.Lpu_id = TTGHist.Lpu_did or TTGHist.Lpu_did is null)  
order by 8,9      
