-- Установка обязательныех полей в MEDICAL_EPISODE

-- Проставление Амбулаторной карты из Случая обращения
UPDATE AKUZ.T_MEDICAL_EPISODE me
SET AmbulanceCard = eb.AmbulanceCard
FROM AKUZ.T_EVENT_BASE eb
WHERE eb.EventID = me.AmbulanceEvent;

-- Проставление Амбулаторной карты и Случая обращения из Данных по госпитализации
UPDATE AKUZ.T_MEDICAL_EPISODE me
SET AmbulanceCard = subquery.AmbulanceCard,
    AmbulanceEvent = subquery.EventID
FROM (
    SELECT eb.AmbulanceCard, eb.EventID, me.MedicalEpisodeID
    FROM AKUZ.T_MEDICAL_EPISODE me
    JOIN AKUZ.T_MOVEMENTS_DIVISION_CARD mdc ON mdc.MedicalEpisodeID = me.MedicalEpisodeID
    JOIN AKUZ.T_MOVEMENT m ON m.MovementID = mdc.Movement
    JOIN AKUZ.T_EVENT_BASE eb ON eb.EventID = m.EmergencyEvent
    WHERE me.AmbulanceCard IS NULL OR me.AmbulanceEvent IS NULL
) AS subquery
WHERE me.MedicalEpisodeID = subquery.MedicalEpisodeID;

-- Проставление Амбулаторной карты и Случая обращения из Первичного осмотра в поликлинике
UPDATE AKUZ.T_MEDICAL_EPISODE me
SET AmbulanceCard = subquery.AmbulanceCard,
    AmbulanceEvent = subquery.EventID
FROM (
    SELECT eb.AmbulanceCard, eb.EventID, me.MedicalEpisodeID
    FROM AKUZ.T_MEDICAL_EPISODE me
    JOIN AKUZ.T_MEDICAL_EXAMINATION_DATA med ON med.MedicalEpisodeID = me.MedicalEpisodeID
    JOIN AKUZ.T_AMBULANCE_CARD_RECORD acr ON acr.MedicalRecordID = med.MedicalExaminationMain
    JOIN AKUZ.T_EVENT_BASE eb ON eb.EventID = acr.AmbulanceEvent
    WHERE me.AmbulanceCard IS NULL OR me.AmbulanceEvent IS NULL
) AS subquery
WHERE me.MedicalEpisodeID = subquery.MedicalEpisodeID;


-- Установка обязательности на поля
ALTER TABLE AKUZ.T_MEDICAL_EPISODE ALTER COLUMN AmbulanceCard SET NOT NULL;
ALTER TABLE AKUZ.T_MEDICAL_EPISODE ALTER COLUMN AmbulanceEvent SET NOT NULL;