---- Добавление записей в AmbulanceCardRecord
INSERT INTO
    AKUZ.T_AMBULANCE_CARD_RECORD (AmbulanceCard, MedicalRecordID, AmbulanceEvent, PostingDate, Movement, PatientBase)
SELECT
    COALESCE(acr.AmbulanceCard, acr1.AmbulanceCard),
    i.MedicalRecordID,
    COALESCE(acr.AmbulanceEvent, acr1.AmbulanceEvent),
    mr.RecordDate,
    COALESCE(acr.Movement, acr1.Movement),
    COALESCE(acr.PatientBase, acr1.PatientBase)
FROM AKUZ.T_MENTAL_DISPANSERISATION_PRESENCE i
JOIN AKUZ.T_MEDICAL_RECORD mr ON mr.MedicalRecordId = i.MedicalRecordId
LEFT JOIN AKUZ.T_AMBULANCE_CARD_RECORD acr ON acr.MedicalRecordID = i.MedicalExamination
JOIN AKUZ.T_AMBULANCE_CARD_RECORD acr1 ON acr1.MedicalRecordID = i.MentalDispanserisationInit
GO

INSERT INTO
    AKUZ.T_AMBULANCE_CARD_RECORD (AmbulanceCard, MedicalRecordID, AmbulanceEvent, PostingDate, Movement, PatientBase)
SELECT
    acr.AmbulanceCard,
    i.MedicalRecordID,
    acr.AmbulanceEvent,
    acr.PostingDate,
    acr.Movement,
    acr.PatientBase
FROM AKUZ.T_DISPANSERISATION_EVENTS i
JOIN AKUZ.T_AMBULANCE_CARD_RECORD acr ON acr.MedicalRecordID = i.DispanserisationInit
GO

INSERT INTO
    AKUZ.T_AMBULANCE_CARD_RECORD (AmbulanceCard, MedicalRecordID, AmbulanceEvent, PostingDate, Movement, PatientBase)
SELECT
    acr.AmbulanceCard,
    i.MedicalRecordID,
    acr.AmbulanceEvent,
    i.ResultDate,
    acr.Movement,
    acr.PatientBase
FROM AKUZ.T_MANTU_RESULT i
JOIN AKUZ.T_AMBULANCE_CARD_RECORD acr ON acr.MedicalRecordID = i.MantuAccept
GO

INSERT INTO
    AKUZ.T_AMBULANCE_CARD_RECORD (AmbulanceCard, MedicalRecordID, AmbulanceEvent, PostingDate, Movement, PatientBase)
SELECT
    acr.AmbulanceCard,
    i.MedicalRecordID,
    acr.AmbulanceEvent,
    i.DateEditing,
    acr.Movement,
    acr.PatientBase
FROM AKUZ.T_DISPANSERISATION_MOVENMENT i
JOIN AKUZ.T_AMBULANCE_CARD_RECORD acr ON acr.MedicalRecordID = i.DispanserisationInit
GO

INSERT INTO
    AKUZ.T_AMBULANCE_CARD_RECORD (AmbulanceCard, MedicalRecordID, AmbulanceEvent, PostingDate, Movement, PatientBase)
SELECT
    acr.AmbulanceCard,
    i.MedicalRecordID,
    acr.AmbulanceEvent,
    i.DateClose,
    acr.Movement,
    acr.PatientBase
FROM AKUZ.T_MEDICAL_CERTIFICATE_CLOSE i
JOIN AKUZ.T_AMBULANCE_CARD_RECORD acr ON acr.MedicalRecordID = i.MedicalCertificate
GO

INSERT INTO
    AKUZ.T_AMBULANCE_CARD_RECORD (AmbulanceCard, MedicalRecordID, AmbulanceEvent, PostingDate, Movement, PatientBase)
SELECT
    acr.AmbulanceCard,
    i.MedicalRecordID,
    acr.AmbulanceEvent,
    i.ResultDate,
    acr.Movement,
    acr.PatientBase
FROM AKUZ.T_REACTIONS_ON_VACCINATION i
JOIN AKUZ.T_AMBULANCE_CARD_RECORD acr ON acr.MedicalRecordID = i.InoculationAccept
GO

INSERT INTO
    AKUZ.T_AMBULANCE_CARD_RECORD (AmbulanceCard, MedicalRecordID, AmbulanceEvent, PostingDate, Movement, PatientBase)
SELECT
    ACR.AmbulanceCard,
    i.MedicalRecordID,
    acr.AmbulanceEvent,
    i.ResultDate,
    acr.Movement,
    acr.PatientBase
FROM AKUZ.T_BCG_RESULT i
JOIN AKUZ.T_AMBULANCE_CARD_RECORD acr ON acr.MedicalRecordID = i.BcgAccept
GO

INSERT INTO
    AKUZ.T_AMBULANCE_CARD_RECORD (AmbulanceCard, MedicalRecordID, AmbulanceEvent, PostingDate, Movement, PatientBase)
SELECT
    acr.AmbulanceCard,
    i.MedicalRecordID,
    acr.AmbulanceEvent,
    i.DateEditing,
    acr.Movement,
    acr.PatientBase
FROM AKUZ.T_MENTAL_DISPANSERISATION_MOVENMENT i
JOIN AKUZ.T_AMBULANCE_CARD_RECORD acr ON acr.MedicalRecordID = i.DispanserisationInit
GO


--- Удаление фк с MedicalRecord
ALTER TABLE AKUZ.T_MENTAL_DISPANSERISATION_PRESENCE
    DROP CONSTRAINT FK_T_MEDICAL_RECORD_T_MENTAL_DISPANSERISATION_PRESENCE_MedicalRecordID
GO

ALTER TABLE AKUZ.T_DISPANSERISATION_EVENTS
    DROP CONSTRAINT FK_T_MEDICAL_RECORD_T_DISPANSERISATION_EVENTS_MedicalRecordID
GO

ALTER TABLE AKUZ.T_MANTU_RESULT
    DROP CONSTRAINT FK_T_MEDICAL_RECORD_T_MANTU_RESULT_MEDICALRECORDID
GO

ALTER TABLE AKUZ.T_DISPANSERISATION_MOVENMENT
    DROP CONSTRAINT FK_T_MEDICAL_RECORD_T_DISPANSERISATION_MOVENMENT_MEDICALRECORDID
GO

ALTER TABLE AKUZ.T_MEDICAL_CERTIFICATE_CLOSE
    DROP CONSTRAINT FK_T_MEDICAL_RECORD_T_MEDICAL_CERTIFICATE_CLOSE_MEDICALRECORDID
GO

ALTER TABLE AKUZ.T_REACTIONS_ON_VACCINATION
    DROP CONSTRAINT FK_T_MEDICAL_RECORD_T_REACTIONS_ON_VACCINATION_MEDICALRECORDID
GO

ALTER TABLE AKUZ.T_BCG_RESULT
    DROP CONSTRAINT FK_T_MEDICAL_RECORD_T_BCG_RESULT_MEDICALRECORDID
GO

ALTER TABLE AKUZ.T_MENTAL_DISPANSERISATION_MOVENMENT
    DROP CONSTRAINT FK_T_MEDICAL_RECORD_T_MENTAL_DISPANSERISATION_MOVENMENT_MedicalRecordID
GO


--- Добавление фк на ambulance_card_record
ALTER TABLE AKUZ.T_MENTAL_DISPANSERISATION_PRESENCE
    ADD CONSTRAINT FK_T_AMBULANCE_CARD_RECORD_T_MENTAL_DISPANSERISATION_PRESENCE_MedicalRecordID FOREIGN KEY (MedicalRecordID) REFERENCES AKUZ.T_AMBULANCE_CARD_RECORD (MedicalRecordID)
GO

ALTER TABLE AKUZ.T_DISPANSERISATION_EVENTS
    ADD CONSTRAINT FK_T_AMBULANCE_CARD_RECORD_T_DISPANSERISATION_EVENTS_MedicalRecordID FOREIGN KEY (MedicalRecordID) REFERENCES AKUZ.T_AMBULANCE_CARD_RECORD (MedicalRecordID)
GO

ALTER TABLE AKUZ.T_MANTU_RESULT
    ADD CONSTRAINT FK_T_AMBULANCE_CARD_RECORD_T_MANTU_RESULT_MEDICALRECORDID FOREIGN KEY (MedicalRecordID) REFERENCES AKUZ.T_AMBULANCE_CARD_RECORD (MedicalRecordID)
GO

ALTER TABLE AKUZ.T_DISPANSERISATION_MOVENMENT
    ADD CONSTRAINT FK_T_AMBULANCE_CARD_RECORD_T_DISPANSERISATION_MOVENMENT_MEDICALRECORDID FOREIGN KEY (MedicalRecordID) REFERENCES AKUZ.T_AMBULANCE_CARD_RECORD (MedicalRecordID)
GO

ALTER TABLE AKUZ.T_MEDICAL_CERTIFICATE_CLOSE
    ADD CONSTRAINT FK_T_AMBULANCE_CARD_RECORD_T_MEDICAL_CERTIFICATE_CLOSE_MEDICALRECORDID FOREIGN KEY (MedicalRecordID) REFERENCES AKUZ.T_AMBULANCE_CARD_RECORD (MedicalRecordID)
GO

ALTER TABLE AKUZ.T_REACTIONS_ON_VACCINATION
    ADD CONSTRAINT FK_T_AMBULANCE_CARD_RECORD_T_REACTIONS_ON_VACCINATION_MEDICALRECORDID FOREIGN KEY (MedicalRecordID) REFERENCES AKUZ.T_AMBULANCE_CARD_RECORD (MedicalRecordID)
GO

ALTER TABLE AKUZ.T_BCG_RESULT
    ADD CONSTRAINT FK_T_AMBULANCE_CARD_RECORD_T_BCG_RESULT_MEDICALRECORDID FOREIGN KEY (MedicalRecordID) REFERENCES AKUZ.T_AMBULANCE_CARD_RECORD (MedicalRecordID)
GO

ALTER TABLE AKUZ.T_MENTAL_DISPANSERISATION_MOVENMENT
    ADD CONSTRAINT FK_T_AMBULANCE_CARD_RECORD_T_MENTAL_DISPANSERISATION_MOVENMENT_MedicalRecordID FOREIGN KEY (MedicalRecordID) REFERENCES AKUZ.T_AMBULANCE_CARD_RECORD (MedicalRecordID)
GO

