--http://bi.mz.mosreg.ru/#edit/etl/809

--https://jira.is-mis.ru/browse/PROMEDWEB-59920
-- Заявочные кампании 2022 по ЛЛО (MS)
with prm as (
select
null::bigint as Lpu_id/*= 13100875*/,
'01.01.2021'::timestamp as BegDate,
'31.12.2022'::timestamp as EndDate)



select L.Lpu_Nick as "МО",
       L.Lpu_OGRN as "ОГРН",
       coalesce(LU.LpuBuilding_Name, '') as "Структурное подразделение",
       coalesce(LU.LpuUnit_Name, '') as "Группа отделений" --DrR.LpuUnit_id,
,
       coalesce(LS.LpuSection_FullName, '') as "Отделение" --DrR.LpuSection_id,
,
       rtrim(coalesce(LR.LpuRegion_Name, '')) || rtrim(coalesce(' ' || LR.LpuRegionType_Name, '')) as "Участок" --DrR.LpuRegion_id,
,
       coalesce(MP.Person_FIO, '') as "Врач" --DrR.MedPersonal_id,
,
       DrR.DrugRequest_id as "Идентификатор заявки",
       DrR.DrugRequest_insDT as "Дата внесения",
       DrR.DrugRequest_updDT as "Дата изменения",
       DrRS.DrugRequestStatus_Name as "Статус",
       rtrim(coalesce(PS.Person_SurName, '')) || rtrim(' ' || coalesce(PS.Person_FirName, '')) || rtrim(' ' || coalesce(PS.Person_SecName, '')) as "ФИО",
       PS.Person_BirthDay as "ДР",
       Mnn.DrugComplexMnn_RusName as "МНН Наименование",
       MnnCode.DrugComplexMnnCode_Code "МНН Код",
       DrR2.DrugRequest_Name as "Вид заявки",
       DrRPO.DrugRequestPersonOrder_OrdKolvo as "кол-во",
       x.mainsum * DrRPO.DrugRequestPersonOrder_OrdKolvo as "Сумма по коду МНН",
       DrLR.DrugListRequest_Comment as "Код закупки",
       DrRPO.DrugRequestPersonOrder_insDT as "Дата внесения позиции",
       DrRPO.DrugRequestPersonOrder_updDT as "Дата изменения позиции"
from dbo.v_DrugRequest DrR
     inner join dbo.v_Lpu L on L.Lpu_id = DrR.Lpu_id and (L.Lpu_id = (select Lpu_id from prm)
 or (select Lpu_id from prm)
     is null and coalesce(L.Lpu_IsTest, 1) = 1) and cast (DrR.DrugRequest_insDT as date) between (select BegDate from prm)
 and (select EndDate from prm)
 and DrR.PersonRegisterType_id in (22, 21)
     --and DrR.DrugRequestStatus_id in (1,8) -- select * from DrugRequestStatus
 and DrR.DrugRequestPeriod_id in (62104, 62105, 62106, 62107, 62109, 62110, 62111, 62112,500801000000005, 500801000000006, 500801000000007, 500801000000008,
   500801000000026, 500801000000027, 500801000000029, 500801000000028, 500801000000015, 500801000000013, 500801000000011, 500801000000009, 500801000000016, 500801000000014, 500801000000012, 500801000000010, 
   500801000000030, 500801000000032, 500801000000031, 500801000000033, 500801000000034, 500801000000036, 500801000000035, 500801000000037, 500801000000024, 500801000000022, 500801000000020, 500801000000018, 500801000000025, 500801000000023, 500801000000021, 500801000000019

)
 and DrR.DrugRequestKind_id in (1,2) and DrR.DrugRequest_Version is null
     left join dbo.v_LpuRegion LR on LR.LpuRegion_id = DrR.LpuRegion_id
     left join dbo.v_LpuSection LS on LS.LpuSection_id = coalesce(DrR.LpuSection_id, LR.LpuSection_id)
     left join dbo.v_LpuUnit LU on LU.LpuUnit_id = coalesce(LS.LpuUnit_id, DrR.LpuUnit_id)
     left join dbo.v_MedPersonal MP on MP.MedPersonal_id = DrR.MedPersonal_id and MP.Lpu_id = DrR.Lpu_id
     left join dbo.v_DrugRequestStatus DrRS on DrRS.DrugRequestStatus_id = DrR.DrugRequestStatus_id
     LEFT JOIN LATERAL
     (
       select DrRSZero.DrugRequestStatus_id
       from dbo.v_DrugRequestStatus DrRSZero
       where DrRSZero.DrugRequestStatus_Code = 4
       limit 1
     ) DrRSZero on true
     LEFT JOIN LATERAL
     (
       select DrR2.DrugRequest_Name,
              DrugRequestProperty_id
       from dbo.v_DrugRequest DrR2
       where DrR2.DrugRequestCategory_id = 5 -- Заявка региона
             and
             coalesce(DrR2.DrugRequest_Version, 1) = 1 and
             DrR2.DrugRequestStatus_id <> DrRSZero.DrugRequestStatus_id and
             coalesce(DrR2.DrugRequestKind_id, 0) = coalesce(DrR.DrugRequestKind_id, 0) and
             coalesce(DrR2.DrugRequestPeriod_id, 0) = coalesce(DrR.DrugRequestPeriod_id, 0) and
             coalesce(DrR2.DrugGroup_id, 0) = coalesce(DrR.DrugGroup_id, 0) and
             coalesce(DrR2.PersonRegisterType_id, 0) = coalesce(DrR.PersonRegisterType_id, 0)
       order by DrR2.DrugRequest_id desc
       limit 1
     ) DrR2 on true
     left join v_DrugRequestPersonOrder DrRPO on DrRPO.DrugRequest_id = DrR.DrugRequest_id and (DrRPO.DrugComplexMnn_id is not null or DrRPO.Tradenames_id is not null)
     left join dbo.v_PersonState PS on PS.Person_id = DrRPO.Person_id
     left join rls.v_DrugComplexMnn Mnn on Mnn.DrugComplexMnn_id = DrRPO.DrugComplexMnn_id
     LEFT JOIN LATERAL
     (
       select *
       from rls.v_DrugComplexMnnCode MnnCode
       where MnnCode.DrugComplexMnn_id = Mnn.DrugComplexMnn_id
       order by MnnCode.DrugComplexMnnCode_updDT desc
       limit 1
     ) MnnCode on true
     --left join rls.v_DrugComplexMnnCode MnnCode  on MnnCode.DrugComplexMnn_id = Mnn.DrugComplexMnn_id
     left join
     (
       select DrugRequest_id,
              DrugComplexMnn_id,
              case
                when DrugRequestRow_Kolvo > 0 then coalesce(DrugRequestRow_Summa, 0) / DrugRequestRow_Kolvo
                else 0
              end as mainsum
       from dbo.v_DrugRequestRow x
     ) x on x.DrugRequest_id = DrR.DrugRequest_id and x.DrugComplexMnn_id = DrRPO.DrugComplexMnn_id
     LEFT JOIN LATERAL
     (
       select DrLR.DrugListRequest_Comment
       from dbo.v_DrugListRequest DrLR
       where (DrLR.DrugComplexMnn_id = DrRPO.DrugComplexMnn_id) and
             (DrLR.DrugRequestProperty_id = DrR2.DrugRequestProperty_id)
       limit 1
     ) DrLR on true
order by L.Lpu_Nick,
         coalesce(LU.LpuBuilding_Name, ''),
         coalesce(LU.LpuUnit_Name, ''),
         coalesce(LS.LpuSection_FullName, ''),
         coalesce(MP.Person_FIO, ''),
         DrR.DrugRequest_insDT,
         Mnn.DrugComplexMnn_RusName,
         rtrim(coalesce(PS.Person_SurName, '')) || rtrim(' ' || coalesce(PS.Person_FirName, '')) || rtrim(' ' || coalesce(PS.Person_SecName, '')),
         PS.Person_BirthDay
