IF OBJECT_ID('AKUZ.V_RSOSZ_HEALTH_OBJECT') IS NOT NULL
    DROP VIEW AKUZ.V_RSOSZ_HEALTH_OBJECT;
GO
CREATE VIEW AKUZ.V_RSOSZ_HEALTH_OBJECT
AS
SELECT NEWID()             AS rsoszhealthobjectid,
       pat.PatientID       AS patient,
       diagnos.MKBCode     AS mkbcode,
       diagnos.FatherName  AS doctorfathername,
       diagnos.FirstName   AS doctorfirstname,
       diagnos.LastName    AS doctorlastname,
       diagnos.Snils       AS doctorsnils,
       diagnos.oid         AS mooid,
       diagnos.postingdate AS postingdate,
       diagnos.diagnosis   AS diagnosis,
       diagnos.typedisease AS diagnosistype
FROM (
    SELECT p.PatientId
    FROM akuz.t_patient p
    JOIN VCLib.T_ENUMS e ON p.Sex = e.EnumID
    WHERE e.Code = 1
     ) AS pat
CROSS APPLY
(
    SELECT mkb.MKBCode    AS mkbcode,
           post.LastName,
           post.FatherName,
           post.FirstName,
           post.Snils,
           mkb.oid,
           mkb.PostingDate,
           mkb308.NameEHR AS diagnosis,
           mkb.typedisease
    FROM (
        SELECT m.MCBCode AS MKBCode,
               mr.Post   AS Post,
               mdr308.oid,
               mr.postingdate,
               CASE
                   WHEN dis.TypeDisease = 1 THEN 2
                   WHEN dis.TypeDisease = 0 THEN 3
                   WHEN dis.TypeDisease IS NULL THEN 1
                   END   AS typedisease,
               ROW_NUMBER()
                   OVER (
                       PARTITION BY ac.patient, m.MCBCode
                       ORDER BY MR.PostingDate ASC
                       ) AS rn
        FROM AKUZ.T_AMBULANCE_CARD ac
        JOIN AKUZ.t_medical_record mr ON mr.AmbulanceCard = ac.AmbulanceCardID
        JOIN AKUZ.T_OUTCOME_OF_INSPECTION oi ON oi.MedicalRecordID = mr.MedicalRecordID
        LEFT JOIN AKUZ.T_ACCOMPANYING_DISEASES dis ON oi.MedicalRecordID = dis.MedicalRecordID
        JOIN AKUZ.T_MKB m ON oi.MKBCode = m.MCBCode
        JOIN AKUZ.T_LPU_MAIN lpu ON ac.CardLpu = lpu.LpuID
        LEFT JOIN EHR.T_EHR_RUS_MDR308 mdr308 ON lpu.EhrRusMdr308 = mdr308.EhrRusID
        WHERE
              ac.patient = pat.PatientID
          AND m.RsoszType = 0
         ) mkb
    CROSS APPLY
    (
        SELECT LastName,
               FatherName,
               FirstName,
               Snils
        FROM AKUZ.T_POST post
        JOIN AKUZ.T_WORKER w ON w.WorkerID = post.Worker
        WHERE post.ResourceID = mkb.Post
    ) post
    CROSS APPLY
    (
        SELECT TOP 1 mkb308.NameEHR
        FROM EHR.T_EHR_RUS_MKB308 mkb308
        JOIN EHR.T_EHR_RUS_REF_BOOKS rb ON mkb308.EhrRusRefBooks = rb.EhrRusRefBooksID
        WHERE
              mkb308.MKB_CODE = mkb.MKBCode
          AND CAST('/' + rb.CurrVersion + '/' AS HIERARCHYID) =
              (
                  SELECT TOP 1 CAST('/' + rb.CurrVersion + '/' AS HIERARCHYID)
                  FROM EHR.T_EHR_RUS_REF_BOOKS rbb
                  WHERE mkb308.EhrRusRefBooks = rbb.EhrRusRefBooksID
                  ORDER BY 1 DESC
              )
    ) AS mkb308
    WHERE rn = 1
) diagnos
GO


