/****** Object:  StoredProcedure [dbo].[QueryForCel1]    Script Date: 23.01.2024 14:48:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[QueryForCel1]
    @god    INT,
    @mes    INT
AS
BEGIN
    SET NOCOUNT ON;


    CREATE TABLE #find (
        idschet     INT,
        idcase      INT,
        god         INT,
        mes         INT,
        kodbaselpu  INT,
        Id          BIGINT,
        vidpom      INT,
        kodmkb      NVARCHAR(10),
        dnach       DATE,
        dkon        DATE,
        kodspec     INT,
        kodprofilgg INT,
        ishodG      INT,
        KZG         INT
    )

    CREATE TABLE #t (
        idschet_1 INT, idcase_1 INT, idschet_2 INT, idcase_2 int
    )

    CREATE TABLE #pair (idschet INT, idcase INT)

    INSERT INTO #find
            ( idschet ,
              idcase,
              god ,
              mes ,
              kodbaselpu ,
              Id ,
              vidpom ,
              kodmkb ,
              dnach ,
              dkon ,
              kodspec ,
              kodprofilgg ,
              ishodG,
              KZG
            )
    SELECT r.idschet, r.idcase, s.year, s.month, r.lpu, r.Id,
    r.usl_ok,
    r.ds1, CAST(r.date_1 AS DATE) AS DNach, CAST(r.date_2 AS DATE) AS DKon, 0/*zd.KodSpec*/, di.KodProfilGG, r.rslt, di.KodDRG
    FROM WORKPLACE.sluch_base r
    JOIN WORKPLACE.sluch_dopinfo di ON di.idschet = r.idschet AND di.idcase = r.idcase
    JOIN dbo.vw_schet s ON s.idschet = r.idschet
    OUTER APPLY (
        SELECT TOP 1
            CASE ISNULL(KodPrichOtkaz,0)
                WHEN 0 THEN 1 -- полная оплата
                ELSE IIF(r.sumv>m.SummaP,3,2) -- если сумма выставленная больше принятой то частичный, иначе полный отказ
            END AS Oplata
        FROM EXPERT.mek m
        LEFT OUTER JOIN EXPERT.ActOut ON ActOut.idact = m.idact
        WHERE m.idschet=r.idschet AND m.idcase=r.idcase AND (m.idact IS NULL OR ActStatusId IN (1,2))
        ORDER BY m.FinSank DESC
        ) mek
    WHERE ((s.year=@god AND s.month<=@mes) OR s.year=@god-1) AND r.usl_ok=1 AND ISNULL(mek.Oplata,0)<>2 AND ISNULL(r.id,0)>0-- AND di.PovodObraj=38
    AND NOT (di.kodprofilgg IN (25,7) AND ( -- онкология и гематология
                                        (di.KodDRG BETWEEN 18146 AND 18155)
                                    OR  (di.KodDRG=18157)
                                    OR  (di.KodDRG BETWEEN 18160 AND 18162)
                                    ))
    AND NOT (kodprofilgg IN (25,7) AND s.SchetType='T' AND di.KodDRG=20022) --4.2018 пункт 1.6
        AND NOT (kodprofilgg IN (25,7) AND (r.ds1 LIKE 'C8[1-9]%' OR r.ds1 LIKE 'C9[0-6]%') AND EXISTS(SELECT * FROM WORKPLACE.usl_base u JOIN DICT.FSMedUsl mu ON u.code_usl=mu.Kod WHERE u.idschet=r.idschet AND u.idcase=r.idcase AND mu.FSKod='A25.30.033.00[1-2]'))
        AND NOT (KodProfilGG IN (10,144,25) AND r.ds1 LIKE 'C%' AND (DATEDIFF(DAY,r.date_1,r.date_2)) BETWEEN 0 AND 4)
        AND NOT (KodProfilGG=28/*офтольмология*/ AND r.ds1 LIKE 'H2[5-6]%' AND EXISTS(SELECT * FROM WORKPLACE.usl_base u JOIN DICT.FSMedUsl mu ON u.code_usl=mu.Kod WHERE u.idschet=r.idschet AND u.idcase=r.idcase AND mu.FSKod='А16.26.09[3-4]') AND di.KodDRG IN (18173,18174))
        AND NOT (KodProfilGG=28/*офтольмология*/ AND r.ds1 LIKE 'H35.3' AND EXISTS(SELECT * FROM WORKPLACE.usl_base u JOIN DICT.FSMedUsl mu ON u.code_usl=mu.Kod WHERE u.idschet=r.idschet AND u.idcase=r.idcase AND mu.FSKod='А16.26.086.001')) --4.2018 пункт 1.7
        AND NOT (KodProfilGG=139/*мед. реабилитиция*/ AND di.KodDRG BETWEEN 18325 AND 18342)
        AND NOT (KodProfilGG IN (26/*гинекология*/,33/*Патологии беременности*/) AND r.ds1 LIKE 'O[0-9][0-9]%' AND di.KodDRG=18002) -- 04.2018 пункт 1.8
        AND NOT (KodProfilGG IN (2,3)/*ревматология и гастро*/ AND di.KodDRG IN (18316,20030)) -- 04.2018 пункт 1.9
        AND NOT (r.ds1 LIKE 'M91.1') -- Болезнь Пертеса
    AND NOT (r.ds1 LIKE 'E76%') -- Мукополисахаридоз -- 04.2018 пункт 1.10

    INSERT INTO #t
            ( idschet_1, idcase_1, idschet_2, idcase_2 )
    SELECT r1.idschet, r1.idcase, r2.idschet, r2.idcase
    FROM (SELECT * FROM #find WHERE god=@god AND mes<=@mes) r1
    JOIN (SELECT * FROM #find) r2 ON r2.Id = r1.Id
    WHERE ((r1.idschet<>r2.idschet) OR (r1.idschet=r2.idschet AND r1.idcase<>r2.idcase))
    AND (r1.kodbaselpu=r2.KodBaseLPU AND r1.KodProfilGG=r2.kodprofilgg AND r1.KodMKB=r2.KodMKB)
    AND ((DATEDIFF(DAY,r2.DKon,r1.DNach)) BETWEEN 0 AND 30 or (DATEDIFF(DAY,r1.DKon,r2.DNach)) BETWEEN 0 AND 30)
    AND ((r1.dnach<=r2.dnach AND r1.ishodG=101) OR (r2.dnach<=r1.dnach AND r2.ishodG=101)) -- выбираем только случаи у которых первый более ранний случай с исходом выписан из стационара



    INSERT INTO #pair (idschet, idcase)
    SELECT idschet_1, idcase_1 FROM #t

    INSERT INTO #pair (idschet, idcase)
    SELECT idschet_2, idcase_2 FROM #t

    SELECT r.* FROM dbo.vw_reestr r
    JOIN (SELECT DISTINCT idschet, idcase FROM #pair) p ON p.idschet = r.idschet AND p.idcase = r.idcase
    ORDER BY r.Id, r.date_1, r.date_2

    DROP TABLE #find
    DROP TABLE #t
    DROP TABLE #pair

END