---

USE [replTransVas];
GO
SET NOCOUNT ON;

SELECT TOP (200)
    src.kod AS kodReport,
    src.kodMember,
    src.srvsDT,

    CASE WHEN r.kod IS NULL THEN 0 ELSE 1 END AS reportInReplica,
    src.tipReport AS sourceTipReport,
    r.tipReport AS replicaTipReport,
    src.formatCon2 AS sourceFormat,
    r.formatCon2 AS replicaFormat,
    src.listenerCntInteger AS sourceCount,
    r.listenerCntInteger AS replicaCount,
    r.a1,

    m.kodMerop,
    m.beInOn AS memberBeInOn,
    m.timeInMerop,
    p.timeInMerop AS projectThreshold,
    a.clsMerop,
    a.nmMerop,
    w.kodProject AS eventProject,
    a.kodWave,
    mw.maxWave,

    c.certificateRows,
    c.formReceived,
    c.hasCertificate,
    j.mailRows,
    j.fallbackApplied,
    j.internalSent,

    CASE WHEN EXISTS (
        SELECT 1
        FROM dbo.K_tbl_Stasik AS st
        WHERE st.MEMBER_kod = src.kodMember
    ) THEN 1 ELSE 0 END AS memberInStasik,

    CASE
        WHEN r.kod IS NULL
            THEN N'Отчёт отсутствует в replTransVas'
        WHEN m.kod IS NULL
            THEN N'Нет связанного участника в replTransVas'
        WHEN a.kod IS NULL
            THEN N'Нет мероприятия'
        WHEN w.kod IS NULL
            THEN N'Нет волны'
        WHEN mw.maxWave IS NULL OR a.kodWave <> mw.maxWave
            THEN N'Не последняя волна проекта'
        WHEN NOT EXISTS (
            SELECT 1 FROM dbo.trn_project AS tp
            WHERE tp.kod = w.kodProject
        )
            THEN N'Нет проекта в trn_project'
        WHEN ISNULL(a.clsMerop, 0) <> 1
            THEN N'Мероприятие не закрыто'
        WHEN a.nmMerop IS NULL OR a.nmMerop LIKE N'%тест%'
            THEN N'Название мероприятия не проходит фильтр'
        WHEN m.beInOn IS NULL OR m.beInOn NOT IN (0, 1)
            THEN N'Участник не проходит beInOn'
        WHEN m.timeInMerop IS NULL
          OR p.timeInMerop IS NULL
          OR m.timeInMerop <= p.timeInMerop
            THEN N'Не выполнен строгий порог времени'
        WHEN EXISTS (
            SELECT 1
            FROM dbo.sb_reports AS newer
            WHERE newer.kodMember = r.kodMember
              AND newer.kod > r.kod
              AND (
                  newer.emZayavka = r.emZayavka
                  OR (newer.emZayavka IS NULL AND r.emZayavka IS NULL)
              )
              AND (
                  newer.a1 = r.a1
                  OR (newer.a1 IS NULL AND r.a1 IS NULL)
              )
        )
            THEN N'Есть более новый отчёт в той же группе'
        WHEN (
            r.a1 LIKE N'otzyvMTS[_]%'
            OR c.certificateRows > 0
            OR j.mailRows > 0
        )
        THEN
            CASE
                WHEN m.beInOn <> 1
                    THEN N'МТС: beInOn не равен 1'
                WHEN NULLIF(LTRIM(RTRIM(r.formatCon2)), N'') IS NULL
                    THEN N'МТС: пустой formatCon2'
                WHEN ISNULL(r.listenerCntInteger, 0) <= 0
                    THEN N'МТС: количество участников не положительное'
                WHEN ISNULL(c.formReceived, 0) <> 1
                 AND ISNULL(j.fallbackApplied, 0) <> 1
                    THEN N'МТС: нет подтверждения формы'
                WHEN ISNULL(c.hasCertificate, 0) <> 1
                 AND ISNULL(j.internalSent, 0) <> 1
                    THEN N'МТС: нет подтверждения сертификата'
                ELSE N'Основные фильтры пройдены: проверить запуск и Тильда-ветку'
            END
        WHEN m.beInOn = 0 AND r.otzyv IS NULL
            THEN N'beInOn=0 и отсутствует отзыв'
        ELSE N'Основные фильтры пройдены: проверить запуск и Тильда-ветку'
    END AS firstBlockingReason

FROM myNewVasya.dbo.sb_reports AS src
LEFT JOIN dbo.sb_reports AS r
    ON r.kod = src.kod
LEFT JOIN dbo.dat_members AS m
    ON m.kod = r.kodMember
LEFT JOIN dbo.dat_activity AS a
    ON a.kod = m.kodMerop
LEFT JOIN dbo.spr_wave AS w
    ON w.kod = a.kodWave
LEFT JOIN dbo.spr_project AS p
    ON p.kod = w.kodProject
LEFT JOIN dbo.srvs_maxWave AS mw
    ON mw.kodProject = w.kodProject

OUTER APPLY (
    SELECT
        COUNT(*) AS certificateRows,
        MAX(CONVERT(int, ct.formReceived)) AS formReceived,
        MAX(CASE
            WHEN NULLIF(LTRIM(RTRIM(ct.mtsCertNumber)), N'') IS NOT NULL
            THEN 1 ELSE 0
        END) AS hasCertificate
    FROM myNewVasya.dbo.dat_mts_certificates AS ct
    WHERE ct.kodReport = r.kod
      AND ct.kodMember = r.kodMember
) AS c

OUTER APPLY (
    SELECT
        COUNT(*) AS mailRows,
        MAX(CASE
            WHEN ml.kind = N'fallback_certificate'
             AND ml.state IN (N'QUEUED', N'SENT')
            THEN 1 ELSE 0
        END) AS fallbackApplied,
        MAX(CASE
            WHEN ml.kind IN (N'fallback_certificate', N'correction')
             AND ml.state IN (N'QUEUED', N'SENT')
             AND s.fromEmail = N'DashaMail'
             AND s.sendEM = 1
             AND s.dtSend IS NOT NULL
             AND (
                 UPPER(LTRIM(s.resultSend)) LIKE N'OK%'
                 OR UPPER(s.resultSend)
                    LIKE N'%' + CHAR(13) + CHAR(10) + N'OK%'
                 OR UPPER(s.resultSend)
                    LIKE N'%' + CHAR(10) + N'OK%'
             )
            THEN 1 ELSE 0
        END) AS internalSent
    FROM myNewVasya.dbo.mts_daemon_mail AS ml
    LEFT JOIN myNewVasya.dbo.sb_send AS s
        ON s.kod = ml.sendKod
       AND s.kodReport = ml.kodReport
       AND s.kodMember = ml.kodMember
    WHERE ml.kodReport = r.kod
      AND ml.kodMember = r.kodMember
) AS j

WHERE src.kodProject = 8
  AND NOT EXISTS (
      SELECT 1
      FROM dbo.K_tbl_Stasik AS st
      WHERE st.kodReport = src.kod
  )
ORDER BY src.kod DESC;

-- Проверяем, доходил ли sp_start до обновления Стасика.
SELECT TOP (100)
    datetime_sp,
    note
FROM dbo.K_log_sp_start
ORDER BY datetime_sp DESC;