---

USE [replTransVas];
GO

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

ALTER VIEW [dbo].[view_otyvMember]
AS
WITH ReportRows AS
(
    -- Основной путь: дата мероприятия заполнена.
    SELECT
        r.kod,
        r.kodMember,
        r.tipReport,
        r.region,
        r.nmOrg,
        r.vidOrg,
        r.listenerCntInteger,
        r.manFIO,
        r.otzyv,
        r.kodProject,
        r.meropNm,
        CONVERT(date, a.dtMerop) AS meropDt,
        r.emZayavka,
        m.kodMerop,
        m.timeInMerop,
        a.nmMerop,
        r.processingStatus,
        r.mtsFormLink
    FROM dbo.dat_activity AS a
    INNER JOIN dbo.dat_members AS m
        ON m.kodMerop = a.kod
    INNER JOIN dbo.sb_reports AS r
        ON r.kodMember = m.kod
    INNER JOIN dbo.srvs_maxWave AS mw
        ON mw.maxWave = a.kodWave
       AND mw.kodProject = r.kodProject
    WHERE
        a.dtMerop IS NOT NULL
        AND r.kodProject <> 7

    UNION ALL

    -- Резервный путь: дата берётся из отчёта.
    SELECT
        r.kod,
        r.kodMember,
        r.tipReport,
        r.region,
        r.nmOrg,
        r.vidOrg,
        r.listenerCntInteger,
        r.manFIO,
        r.otzyv,
        r.kodProject,
        r.meropNm,
        TRY_CONVERT(date, r.meropDt, 104) AS meropDt,
        r.emZayavka,
        m.kodMerop,
        m.timeInMerop,
        a.nmMerop,
        r.processingStatus,
        r.mtsFormLink
    FROM dbo.dat_activity AS a
    INNER JOIN dbo.dat_members AS m
        ON m.kodMerop = a.kod
    INNER JOIN dbo.sb_reports AS r
        ON r.kodMember = m.kod
    INNER JOIN dbo.srvs_maxWave AS mw
        ON mw.maxWave = a.kodWave
       AND mw.kodProject = r.kodProject
    WHERE
        a.dtMerop IS NULL
        AND r.kodProject <> 7
)
SELECT
    r.kod,
    N'отчет' AS tip,
    r.kod AS kodReport,
    r.kodMember,
    cert.mtsCertNumber,
    r.tipReport,
    r.region,
    r.nmOrg,
    r.vidOrg,
    r.listenerCntInteger,
    r.manFIO,
    r.otzyv,
    r.kodProject,
    r.meropNm,
    r.meropDt,
    r.emZayavka,
    org.vidOO,
    org.shName,
    r.kodMerop,
    r.timeInMerop,
    r.nmMerop,
    r.processingStatus AS status,
    r.mtsFormLink
FROM ReportRows AS r
OUTER APPLY
(
    SELECT TOP (1)
        e.kodOrg
    FROM dbo.dat_emails AS e
    WHERE e.em = r.emZayavka
    ORDER BY e.kod DESC
) AS lastEmail
LEFT JOIN dbo.dat_organization AS org
    ON org.kod = lastEmail.kodOrg
OUTER APPLY
(
    SELECT
        CASE
            WHEN COUNT_BIG(*) = 1
                THEN MAX(c.mtsCertNumber)
            ELSE NULL
        END AS mtsCertNumber
    FROM myNewVasya.dbo.dat_mts_certificates AS c
    WHERE c.kodReport = r.kod
      AND c.kodMember = r.kodMember
) AS cert;
GO