
SELECT
    ttms.TimeTableMedService_id "TimeTableMedService_id",
    ttms.TimetableType_id "TimetableType_id",
    ttms.EvnDirection_id "EvnDirection_id",
    to_char(ttms.TimeTableMedService_begTime, 'HH24:MI') "TimeTableMedService_begTime",
    to_char(ttms.TimeTableMedService_factTime, 'HH24:MI') "TimeTableMedService_factTime",
    case
        when ttms.TimeTableMedService_begTime is not null then to_char(ttms.TimeTableMedService_begTime, 'dd.mm.yyyy')
        when ttms.TimeTableMedService_factTime is not null then to_char(ttms.TimeTableMedService_factTime, 'dd.mm.yyyy')
        else to_char(ttms.TimeTableMedService_insDT, 'dd.mm.yyyy')
    end as "TimeTableMedService_Date",
    veda.EvnDirection_pid "EvnDirection_pid",
    veda.EvnDirection_Num "EvnDirection_Num",
    vevp.EvnVizitPL_id "EvnVizitPL_id",
    vevp.EvnVizitPL_pid "EvnVizitPL_pid",
    ps.Person_id "Person_id",
    ps.Server_id "Server_id",
    ps.PersonEvn_id "PersonEvn_id",
    -- ФИО пациента
    CONCAT_WS(' ', ps.Person_SurName, ps.Person_FirName, ps.Person_SecName) as "Person_FIO",
    ms.MedService_id "MedService_id",
    ms.MedService_Name "MedService_Name",
    ms.MedService_Nick "MedService_Nick",
    ms.LpuSection_id "LpuSection_id",
    ms.Lpu_id "Lpu_id",
    mst.MedServiceType_SysNick "MedServiceType_SysNick",
    mst.MedServiceType_id "MedServiceType_id",
    uc.UslugaComplex_id "UslugaComplex_id",
    ucms.UslugaComplexMedService_id "UslugaComplexMedService_id",
    --Дата рождения
    to_char(ps.Person_BirthDay, 'dd.mm.yyyy') as "Person_BirthDay",
    --Статус вакцинации: 0-Назначено, 1-Исполнено, 2-Медотвод (отдельно)
    case vja.vacJournalAccount_StatusType_id
        when 0 then 'Назначено'
        when 1 then 'Исполнено'
        else vjmtr.vacJournalMedTapRefusal_NameTypeRec
    end as "VacStatus_Name"
FROM v_MedService ms
    INNER JOIN v_MedServiceType mst  ON mst.MedServiceType_id = ms.MedServiceType_id
    INNER JOIN v_UslugaComplexMedService ucms  ON ucms.MedService_id = ms.MedService_id
    INNER JOIN v_UslugaComplex uc  ON uc.UslugaComplex_id = ucms.UslugaComplex_id
    INNER JOIN v_UslugaCategory ucat ON uc.UslugaCategory_id = ucat.UslugaCategory_id
    INNER JOIN v_TimeTableMedService ttms  ON ttms.UslugaComplexMedService_id = ucms.UslugaComplexMedService_id
    LEFT JOIN LATERAL (select p.* from v_PersonState p where p.Person_id = ttms.Person_id limit 1) ps on true
    -- EvnDirection_id -> EvnVizitPL_id	-> (vac_JournalAccount, vac_JournalMedTapRefusal)
    LEFT JOIN v_EvnDirection_all veda  ON veda.EvnDirection_id = ttms.EvnDirection_id
    LEFT JOIN v_EvnVizitPL vevp  ON vevp.EvnDirection_id = veda.EvnDirection_id
    -- Vacc
    LEFT JOIN vac.v_vac_JournalAccount vja
        ON vja.EvnVizitPL_id = vevp.EvnVizitPL_id
        AND vja.vacJournalAccount_StatusType_id in (0, 1) 		--Назначено, исполнено
    LEFT JOIN vac.Inoculation i
        ON i.vacJournalAccount_id = vja.vacJournalAccount_id
        AND i.VaccineType_id IN (130, 131) 			--Первичная, вторичная вакцина в списке прививок
    -- Refuse
    LEFT JOIN vac.v_JournalMedTapRefusal vjmtr
        ON vjmtr.EvnVizitPL_id = vevp.EvnVizitPL_id
        AND vjmtr.vacJournalMedTapRefusal_TypeRecord = 1		--Мед. отвод
        AND vjmtr.VaccineType_id in (130, 131)			--Тип вакцины в мед.отводе
WHERE
    1 = 1
     and ms.MedService_id = '890101000001134' --Кабинет вакцинации (ID службы)
    
        AND ms.MedServiceType_id = 31 				--Тип службы
        AND uc.UslugaComplex_Code in ('B04.014.004', 'B04.014.004.007')		--Код услуги
        AND ucat.UslugaCategory_Code in (4, 5)		--Услуга категории: 4. ГОСТ, 5. Услуги ЛПУ
    
    AND ttms.TimetableMedService_begTime BETWEEN '2023-07-26 00:00:00' AND '2023-07-26 23:59:59' --Период дат
    
    
                    and vja.vacJournalAccount_StatusType_id = 1
                    and i.VaccineType_id IN (130, 131)
                
    ORDER BY ttms.TimetableMedService_begTime

