with params as (
	select 
		cast($1 as bigint) as Lpu,
		cast($2 as bigint) as LpuBuilding,
		cast($3 as bigint) as LpuUnit,
		cast($4 as bigint) as LpuSection,
		cast($5 as bigint) as MedPersonal,
		cast($6 as bigint) as IsClosed,
		to_timestamp($7,'DD.MM.YYYY') as BegDate,
		to_timestamp($8,'DD.MM.YYYY') as EndDate
),
  
StickAll as
      (
          SELECT DISTINCT ROW_NUMBER() OVER(partition by esba.Lpu_id, COALESCE(esba.EvnStickBase_Num, '')
          order by case
                     when e_del.Evn_id is not null or esba.EvnStickBase_IsDelQueue = 2 then 2
                     else 1
                   end, esba.EvnStickBase_id desc) as RowNum, --#142769
                   esba.EvnStickBase_id,
                   esba.StickWorkType_id,
                   esba.EvnStickBase_pid,
                   esba.EvnStickBase_mid,
                   esba.Lpu_id,
                   esba.EvnStickBase_IsPaid,
                   esba.Lpu_outid,
                   esba.MedStaffFact_id,
                   e_del.lpu_id lpu_del,
                   esba.EvnStickBase_disDate, --#142769
                   esba.EvnStickBase_setDate, --#142769
                   eswr.evnStickWorkRelease_begDT, --#142769
                   eswr.EvnStickWorkRelease_endDT, --#142769
                   pa.Person_Fio v2,
                   pa.Person_BirthDay v3,
                   esba.EvnStickBase_Num v4,
                   so.StickOrder_Name || CASE
                                          WHEN esba.EvnStickBase_IsOriginal = 2 THEN ' / Дубликат '
                                          WHEN esba.EvnStickBase_IsOriginal = 1 THEN ' / Оригинал '
                                        end v5,
                   CASE
                     WHEN esba.EvnStickBase_consentDT is not null and RES.EvnStickBase_Num is not null then 'Электронный' --ЭЛН
                     ELSE 'На бумажном носителе' --Бумажный ЛВН

                   END             v6,
                   case
                     when esba.EvnStickBase_IsDelQueue = 2 or e_del.Evn_delDT is not null then 'Да'
                     else 'Нет'
                   end v7,
                   e_del.Evn_delDT v16,
                   CASE
                     WHEN esba.EvnStickBase_consentDT is not null and RES.EvnStickBase_Num is not null then l_del.Lpu_Nick --ЭЛН
                     ELSE l_del2.Lpu_Nick --Бумажный ЛВН

                   END             v17,
                   prev_stick.EvnStick_setDate v20,
                   prev_stick_eswr.evnStickWorkRelease_begDT v21
          FROM v_EvnStickBase_all esba
          inner join params p on 1 = 1
               inner join v_Person_All pa on pa.PersonEvn_id = esba.PersonEvn_id
               left join
               (
                 SELECT min(evnStickWorkRelease_begDT) evnStickWorkRelease_begDT,
                        max(EvnStickWorkRelease_endDT) EvnStickWorkRelease_endDT,
                        EvnStickBase_id
                 from v_EvnStickWorkRelease
                 group by EvnStickBase_id
               ) eswr on eswr.EvnStickBase_id = esba.EvnStickBase_id --#142769
               left join StickOrder so on so.StickOrder_id = esba.StickOrder_id
               left join RegistryESStorage res on res.EvnStickBase_id = esba.EvnStickBase_id
               left join v_Evn_del e_del on e_del.Evn_id = esba.EvnStickBase_id -- Дата удаления
               left join
               (
                 SELECT l_del.lpu_id,
                        o_del.org_Nick lpu_Nick
                 FROM lpu l_del
                      left join org o_del on o_del.org_id = l_del.org_id
               ) l_del on l_del.lpu_id = esba.Lpu_did --Удалившая ЛПУ ЭЛН
               left join
               (
                 SELECT l_del2.lpu_id,
                        o_del2.org_Nick lpu_Nick
                 FROM lpu l_del2
                      left join org o_del2 on o_del2.org_id = l_del2.org_id
               ) l_del2 on l_del2.lpu_id = e_del.Lpu_id --Удалившая ЛПУ бумажный
               LEFT JOIN LATERAL
               (
                 SELECT e_fin.*
                 FROM v_EvnStick_all e1
                      left join v_EvnStick_all e2 on e2.EvnStick_id = e1.EvnStick_prid
                      left join v_EvnStick_all e3 on e3.EvnStick_id = e2.EvnStick_prid
                      left join v_EvnStick_all e4 on e4.EvnStick_id = e3.EvnStick_prid
                      left join v_EvnStick_all e5 on e5.EvnStick_id = e4.EvnStick_prid
                      left join v_evnstick_all e_fin on e_fin.EvnStick_id = CASE
                                                                              when e1.EvnStick_prid is null and COALESCE(e1.StickOrder_id, 1) = 1 and COALESCE(e1.StickWorkType_id, 1) = 1 then e1.EvnStick_id
                                                                              when e2.EvnStick_prid is null and COALESCE(e2.StickOrder_id, 1) = 1 and COALESCE(e2.StickWorkType_id, 1) = 1 then e2.EvnStick_id
                                                                              when e3.EvnStick_prid is null and COALESCE(e3.StickOrder_id, 1) = 1 and COALESCE(e3.StickWorkType_id, 1) = 1 then e3.EvnStick_id
                                                                              when e4.EvnStick_prid is null and COALESCE(e4.StickOrder_id, 1) = 1 and COALESCE(e4.StickWorkType_id, 1) = 1 then e4.EvnStick_id
                                                                              when e5.EvnStick_prid is null and COALESCE(e5.StickOrder_id, 1) = 1 and COALESCE(e5.StickWorkType_id, 1) = 1 then e5.EvnStick_id
                                                                            end
                 where e1.EvnStick_id = esba.EvnStickBase_id
                 limit 1
               ) prev_stick on true --Данные о первичном ЛВН
               left join
               (
                 SELECT min(evnStickWorkRelease_begDT) evnStickWorkRelease_begDT,
                        max(EvnStickWorkRelease_endDT) EvnStickWorkRelease_endDT,
                        EvnStickBase_id
                 from v_EvnStickWorkRelease
                 group by EvnStickBase_id
                 union all
                 select min(StickFSSDataGet_FirstBegDate) evnStickWorkRelease_begDT,
                        max(coalesce(StickFSSDataGet_ThirdEndDate, StickFSSDataGet_SecondEndDate, StickFSSDataGet_FirstEndDate)) EvnStickWorkRelease_endDT,
                        EvnStickBase_id
                 from StickFSSDataGet s
                 group by EvnStickBase_id
               ) prev_stick_eswr on prev_stick_eswr.EvnStickBase_id = prev_stick.EvnStick_id
          Where ((esba.Lpu_id = p.Lpu
                and
                1 = CASE
                      WHEN esba.EvnStickBase_setDate between p.BegDate
                and
                p.EndDate
                then 1
                    END) or
                (esba.Lpu_outid = p.Lpu
                and
                1 = CASE
                      WHEN eswr.EvnStickWorkRelease_endDT between p.BegDate
                and
                p.EndDate
                then 1
                    END) or
                p.Lpu
                is null) and
                esba.StickType_id = 1 -- 1. Больничный лист
                and
                (esba.EvnStickBase_setDate between p.BegDate
                and
                p.EndDate
                or
                eswr.EvnStickWorkRelease_endDT between p.BegDate
                and
                p.EndDate) --#142769
                and
                esba.EvnStickBase_IsFSS = 1 and
                (esba.EvnStickBase_Num is not null or
                esba.EvnStickBase_Num = '')),

      ReleaseExodus as (
          SELECT distinct stickAll.EvnStickBase_id,
                 StickAll.v2,
                 StickAll.v3,
                 StickAll.v4,
                 StickAll.v5,
                 StickAll.v6,
                 StickAll.v7,
                 lpu.Lpu_Nick v8,
                 StickAll.EvnStickBase_setDate v9, --#142769
                 StickAll.evnStickWorkRelease_begDT v10, --#142769
                 eswr_msf.Person_Fio v11,
                 l_out.Lpu_Nick v12,
                 StickAll.EvnStickWorkRelease_endDT v13, --#142769
                 StickAll.EvnStickBase_disDate v14, --#142769
                 msf_f.Person_Fio v15,
                 StickAll.v16,
                 StickAll.v17,
                 CASE
                   WHEN StickAll.EvnStickBase_IsPaid = 2 then reg_stat.RegistryESStatus_Name
                   WHEN reg_stat.RegistryESStatus_Name != 'Принят ФСС' then reg_stat.RegistryESStatus_Name
                 END v18,
                 slt.StickLeaveType_Name as v19,
                 StickAll.v20 as v20,
                 StickAll.v21 as v21,
                 CASE
                   WHEN StickAll.Lpu_outid is not null and coalesce(slt.StickLeaveType_Code, '-1') not in ('31') and StickAll.EvnStickBase_disDate is not null then 2 --ЛВН закрыт
                   ELSE 1 --ЛВН открыт; исключен исход 31 Продолжает болеть

                 END           CloseLvn,
                 (case when Dop.Evn_id is not null then 'Дополнительный ЛВН' else 'Основной ЛВН' end)::varchar as lvn_type,		
                 coalesce(EvnStickDay.EvnStick_DayCount, 0) + 
				 case when coalesce(isClosed, 1) = 2 then coalesce(dbo.GetAllEvnStickPred_DaysCount(e.EvnStick_prid), 0) 
				 	else 0 
					end ::int8 as v23, --количество дней нетрудоспособности #PROMEDWEB-40432     
                 osn_d.Diag_Code::varchar as v24,
				 coalesce(epl.e_name, eps.e_name)::varchar as v25,
				 coalesce(epl.evnpl_numcard, eps.evnps_numcard)::varchar as v26
				
          FROM StickAll
          inner join params p on 1 = 1
               inner join
               (
                 SELECT l.lpu_id,
                        o.org_Nick lpu_Nick
                 FROM lpu l
                      left join org o on o.org_id = l.org_id
               ) lpu on lpu.lpu_id = StickAll.lpu_id
               inner join
               (
                 SELECT f_eswr.EvnStickBase_id,
                        eswr.evnStickWorkRelease_begDT,
                        msf.MedPersonal_id,
                        msf.Person_Fio,
                        msf.LpuSection_id
                 FROM (
                        SELECT min(evnStickWorkRelease_begDT) minDate,
                               EvnStickBase_id
                        from EvnStickWorkRelease
                        GROUP BY EvnStickBase_id
                      ) f_eswr
                      inner join EvnStickWorkRelease eswr on eswr.EvnStickBase_id = f_eswr.EvnStickBase_id and eswr.evnStickWorkRelease_begDT = f_eswr.minDate and COALESCE(eswr.EvnStickWorkRelease_IsDraft, 1) = 1
                      inner join v_MedStaffFact msf on msf.MedStaffFact_id = eswr.MedStaffFact_id
               ) eswr_msf on eswr_msf.EvnStickBase_id = StickAll.EvnStickBase_id
               left join v_LpuSection_all LSins on LSins.LpuSection_id = eswr_msf.LpuSection_id
               left join EvnStickDop Dop on Dop.Evn_id = StickAll.EvnStickBase_id
               left join v_EvnStick e on e.EvnStick_id = CASE
                                                           WHEN StickAll.StickWorkType_id = 2 then StickAll.EvnStickBase_pid
                                                           else StickAll.EvnStickBase_id
                                                         end
               left join StickLeaveType slt on slt.StickLeaveType_id = e.StickLeaveType_id
               left join
               (
                 SELECT l_out.lpu_id,
                        o_out.org_Nick lpu_Nick
                 FROM lpu l_out
                      left join org o_out on o_out.org_id = l_out.org_id
               ) l_out on l_out.lpu_id = StickAll.Lpu_outid
               left join v_MedStaffFact msf_f on msf_f.MedStaffFact_id = StickAll.MedStaffFact_id --Врач закрывший ЛН
               left join v_LpuSection_all LSout on LSout.LpuSection_id = msf_f.LpuSection_id
               LEFT JOIN LATERAL
               (
                 SELECT red.Evn_id,
                        reds.RegistryESDataStatus_Name RegistryESStatus_Name
                 FROM RegistryESData red
                      inner join RegistryESDatastatus reds on reds.RegistryESDataStatus_id = red.RegistryESDataStatus_id
                      inner join v_RegistryES r on r.RegistryES_id = red.RegistryES_id
                 where red.Evn_id = StickAll.EvnStickBase_id and
                       r.Lpu_id = p.Lpu
                 ORDER BY red.RegistryESData_insDT DESC
                 LIMIT 1
               ) reg_stat on true   
			   LEFT JOIN LATERAL (
					select
						'ТАП' as e_name,
						epl.EvnPL_NumCard,
						epl.Diag_id 
					from v_EvnPL epl
					where epl.evnPL_id = stickall.evnstickbase_mid 
					limit 1
			   ) epl on true
			   LEFT JOIN LATERAL (
					select
						'КВС' as e_name,
						eps.EvnPS_NumCard,
						eps.Diag_id
					from v_EvnPS eps
					where eps.EvnPS_id = stickall.evnstickbase_mid
					limit 1
			  ) eps on true 
			  left join v_Diag osn_d on osn_d.Diag_id = coalesce(epl.Diag_id, eps.Diag_id) 
			  left join ( --взято из [rpt].[f16vn_r1_2014]
					select 
						r.EvnStickBase_id,	
						min(cast(r.EvnStickWorkRelease_begDT as date)) EvnStickWorkRelease_begDate, 
						max(cast(r.EvnStickWorkRelease_enddt as date)) EvnStickWorkRelease_enddate,
						sum(date_part('day', r.EvnStickWorkRelease_enddt - r.EvnStickWorkRelease_begDT)::int4 + 1) EvnStick_dayCount
					from EvnStickWorkRelease r
					where 
						coalesce(r.EvnStickWorkRelease_deleted,1) = 1
					group by 
						r.EvnStickBase_id
			  ) EvnStickDay on EvnStickDay.EvnStickBase_id = StickAll.EvnStickBase_id
		
          WHERE (LSIns.LpuBuilding_id = p.LpuBuilding
                or
                LSOut.LpuBuilding_id = p.LpuBuilding
                or
                p.LpuBuilding
                is null) and
                (LSIns.LpuUnit_id = p.LpuUnit
                or
                LSOut.LpuUnit_id = p.LpuUnit
                or
                p.LpuUnit
                is null) and
                (LSIns.LpuSection_id = p.LpuSection
                or
                LSOut.LpuSection_id = p.LpuSection
                or
                p.LpuSection
                is null) and
                (msf_f.MedPersonal_id = p.MedPersonal
                or
                eswr_msf.MedPersonal_id = p.MedPersonal
                or
                p.MedPersonal
                is null) and
                (StickAll.EvnStickBase_setDate between p.BegDate
                and
                p.EndDate
                or
                StickAll.EvnStickWorkRelease_endDT between p.BegDate
                and
                p.EndDate) --#142769
                and
                StickAll.v4 is not null and
                StickAll.RowNum = 1 --#142769
      )

      SELECT ROW_NUMBER() OVER(
      ORDER BY r.v4 ASC) v1,
					r.EvnStickBase_id as "EvnStickBase_id",
                    r.v2,
                    r.v3, --#142769
                    r.v4,
                    r.lvn_type,
                    r.v5,
                    r.v6,
                    r.v7,
                    r.v8,
                    r.v9, --#142769
                    r.v10, --#142769
                    r.v11,
                    r.v12,
                    r.v13, --#142769
                    r.v14, --#142769
                    r.v15,
                    r.v16, --#142769
                    r.v17,
                    r.v18,
                    r.v19,
                    r.v20,
                    r.v21,
                    r.CloseLvn as "CloseLvn",
                    r.v23,
                    r.v24,
                    r.v25,
                    r.v26
      FROM (
             SELECT DISTINCT ReleaseExodus.EvnStickBase_id,
                    ReleaseExodus.v2,
                    to_char(ReleaseExodus.v3, 'DD.MM.YYYY') v3, --#142769
                    ReleaseExodus.v4,
                    ReleaseExodus.lvn_type,
                    ReleaseExodus.v5,
                    ReleaseExodus.v6,
                    ReleaseExodus.v7,
                    ReleaseExodus.v8,
                    to_char(ReleaseExodus.v9, 'DD.MM.YYYY') v9, --#142769
                    to_char(ReleaseExodus.v10, 'DD.MM.YYYY') v10, --#142769
                    ReleaseExodus.v11,
                    ReleaseExodus.v12,
                    to_char(ReleaseExodus.v13, 'DD.MM.YYYY') v13, --#142769
                    to_char(ReleaseExodus.v14, 'DD.MM.YYYY') v14, --#142769
                    ReleaseExodus.v15,
                    to_char(ReleaseExodus.v16, 'DD.MM.YYYY') v16, --#142769
                    ReleaseExodus.v17,
                    ReleaseExodus.v18,
                    ReleaseExodus.v19,
                    to_char(ReleaseExodus.v20, 'DD.MM.YYYY') v20,
                    to_char(ReleaseExodus.v21, 'DD.MM.YYYY') v21,
                    ReleaseExodus.CloseLvn,
                    ReleaseExodus.v23,
                    ReleaseExodus.v24,
                    ReleaseExodus.v25,
                    ReleaseExodus.v26
             FROm ReleaseExodus
           ) r
      where (r.CloseLvn = (select p.IsClosed from params p limit 1)
            or
            (select p.IsClosed from params p limit 1)
            is null)
