if exists ( select * from sys.procedures p where p.name = 'sp_OmsSchetLoadAll_Bulk_3_2_LO' ) 
    DROP PROCEDURE [dbo].[sp_OmsSchetLoadAll_Bulk_3_2_LO]
GO

-- =============================================
-- Description: Хранимка bulk загрузки тестовых счетов отсюда: \\DEVOPS_STORAGE\Storage\IES\Test\Счета\
-- =============================================
CREATE PROCEDURE [dbo].[sp_OmsSchetLoadAll_Bulk_3_2_LO]
    @SchetID uniqueidentifier,
    @type int,
    @Worker uniqueidentifier
AS
BEGIN 

    -- ФЛК второго уровня, проверка зависимостей невозможная с помощью XSD
    declare @newline varchar(4) = char(13) + char(10)
    declare @msg varchar(1000) = ''

    --переменные для старых проверок (некоторые и в новых используются)
    declare @ActualDate datetime = (select [DSCHET] from vcscheme.TEMP_SCHET)
    declare @NSCHET varchar(30) =  (select [NSCHET] from vcscheme.TEMP_SCHET)
    declare @filename1 varchar(26) = 
    (
        select top 1 
        [filename]
        from vcscheme.TEMP_ZGLV
    )

    --проверка, что суммы сходятся
    declare @sumsluch decimal
    select @sumsluch =  Sum([SUM_M]) from vcscheme.TEMP_SLUCH
    declare @sumSanks decimal
    select @sumSanks =  Sum([SANK_IT]) from vcscheme.TEMP_Z_SLUCH
    declare @sumschet decimal
    select @sumschet = SUM([SUMMAV]) from vcscheme.TEMP_SCHET
    if (@sumsluch <> @sumschet)
    begin
        set @msg = @newline + 'ОШИБКА В ФАЙЛЕ СЧЕТ-РЕЕСТРА: Сумма на случаях не равна сумме на счете' + @newline
            + 'sumsluch = ' + convert(varchar(100),@sumsluch) + @newline
            + 'sumschet = ' + convert(varchar(100),@sumschet) + @newline
        raiserror(@msg, 18,1)
    end

    update vcscheme.TEMP_ZGLV set ID = '1'
    update vcscheme.TEMP_SCHET set ID = '1'
    update vcscheme.TEMP_ZAP set SchetZapID = newid()
    update vcscheme.TEMP_Z_SLUCH set [SchetSluchAccomplishedID] = newid()
    update vcscheme.TEMP_SANK set [SchetSluchSankID] = newid()
    update vcscheme.TEMP_SANK_SL_ID set [SchetSluchSankSLID] = newid() 
    update vcscheme.TEMP_SLUCH set [SchetSluchID] = newid()
    update vcscheme.TEMP_ONK_SL set SchetSluchOnkID = newid()
    update vcscheme.TEMP_ONK_USL set [SchetOnkUslID] = newid() 
    update vcscheme.TEMP_SANK set [SchetSluchSankID] = newid() 
    update vcscheme.TEMP_KSG_KPG set [KsgID] = newid() 
    update vcscheme.TEMP_USL set [SchetUslID] = newid()

    --Объявляем переменную для строки данных
    DECLARE @IDSP_R VARCHAR(20);
    --Обобщённое табличное выражение
    WITH cte AS 
    (
        --Получаем уникальные значения
        select distinct ssa.IDSP AS IDSP_R 
        FROM vcscheme.TEMP_Z_SLUCH ssa
    )
    --Формируем строку
    SELECT @IDSP_R = ISNULL(@IDSP_R + ', ','') + cast(IDSP_R as varchar(3))
    FROM cte;

     SELECT '[IES].[T_SCHET]' as tableName, 'счета' as operationName, (select count(*) from vcscheme.TEMP_SCHET) as tableCount
                 
    SELECT [VERSION]
            ,[DATA]
            ,[CODE]
            ,[YEAR]
            ,[MONTH]
            ,[NSCHET]
            ,[DSCHET]
            ,[SUMMAV]
            ,[COMENTS]
            --для омс эти поля пустые - требования модуля реэкспертизы
            ,[SUMMAV] as [SUMMAP]
            --,case when @type = 693 then 0 else [SANK_MEK] end as[SANK_MEK]
            ,case when @type = 693 then 0 else [SANK_MEE] end as[SANK_MEE]
            ,case when @type = 693 then 0 else [SANK_EKMP] end as[SANK_EKMP]                     
            --для не омс эти поля пустые, это только для СМО
            ,CAST(CAST(CAST(GETDATE() as float) as INT) as datetime) as [ReceivedDate]            
            ,GETDATE() as [ReceivedTime]
            ,[FILENAME]
            ,[CODE_MO]
            ,[PLAT]
            ,@Worker as [Worker]
            ,@SchetID as [SchetID]
            ,@type as [type_]
            ,0 as [Status]
            ,0 as [SchetKind]
            ,(SELECT V016ID from IES.T_V016_DISPT where IDDT = DISP and DATEBEG<=@ActualDate and (DATEEND is null or DATEEND>=@ActualDate)) AS DISP
            ,SD_Z
            ,null as SchetMain
            ,(SELECT top 1 [filename] from vcscheme.TEMP_PERS_ZGLV) as FILENAME_1
            ,(SELECT COUNT(*) FROM vcscheme.TEMP_Z_SLUCH) as SchetSluchAccomplishedCount
    FROM vcscheme.TEMP_SCHET t1  
        JOIN vcscheme.TEMP_ZGLV t2 on t2.ID = t1.ID
                  
    SELECT '[IES].[T_SCHET_ZAP]' as tableName, ' информации о людях' as operationName, (select count(*) from vcscheme.TEMP_ZAP) as tableCount

    select t0.ID_PAC, Left(t0.rezult,Len(t0.rezult)-1) As rezult into #temp_dost
    From
    (
        Select distinct cast(ID_PAC as varchar(36)) as ID_PAC, 
        (
            Select cast(t1.DOST as varchar(1)) + ',' AS [text()]
            From vcscheme.TEMP_DOST t1
            Where cast(t1.ID_PAC as varchar(36)) = cast(ST2.ID_PAC as varchar(36))
            ORDER BY cast(t1.ID_PAC as varchar(36))
            For XML PATH ('')
        ) rezult
        From vcscheme.TEMP_DOST ST2
    ) t0 

    select t0.ID_PAC, Left(t0.rezult,Len(t0.rezult)-1) As rezult into #temp_dost_p
    From
    (
        Select distinct cast(ID_PAC as varchar(36)) as ID_PAC, 
        (
            Select cast(t1.DOST_P as varchar(1)) + ',' AS [text()]
            From vcscheme.TEMP_DOST_P t1
            Where cast(t1.ID_PAC as varchar(36)) = cast(ST2.ID_PAC as varchar(36))
            ORDER BY cast(t1.ID_PAC as varchar(36))
            For XML PATH ('')
        ) rezult
        From vcscheme.TEMP_DOST_P ST2
    ) t0     

    SELECT [SchetZapID]
            ,[SPOLIS]
            ,[NPOLIS]
            ,[NOVOR]
            ,zap.[N_ZAP]
            ,(SELECT TOP 1 f008.IDDOC FROM [IES].T_F008_OMS_TYPE f008 WHERE f008.IDDOC = [VPOLIS]) as [VPOLIS]
            ,cast(pac.ID_PAC as varchar(36)) as [ID_PAC]
            ,(SELECT TOP 1 mo.SMOCOD FROM [IES].T_F002_SMO mo WHERE mo.SMOCOD = pac.[SMO]) as [SMO]
            ,[SMO_OGRN]
            ,[SMO_OK]
            ,[SMO_NAM]
            ,[PR_NOV]
            ,@SchetID as [Schet]
            ,698 as [type_]
            ,[ST_OKATO]
            ,nullif([VNOV_D],0) as [VNOV_D]
            ,[FAM]
            ,[IM]
            ,[OT]
            ,[W]
            ,[DR]
            ,[FAM_P]
            ,[IM_P]
            ,[OT_P]
            ,[W_P]
            ,[DR_P]
            ,[MR]
            ,[DOCTYPE]
            ,[DOCSER]
            ,[DOCNUM]
            ,[DOCDATE]
            ,[DOCORG]
            ,[SNILS]
            ,[OKATOG]
            ,[OKATOP]
            ,[COMENTP]
            ,[MO] as [AttachmentMo]
            --,[MODATE] as [AttachDate]
            ,dost.rezult as DOST
            ,dost_p.rezult as DOST_P
            ,[INV]
            ,pac.[MSE]
            ,TEL as [TEL]
            ,[ENP]
            --, MO_SK as [MO_SK],
    FROM vcscheme.TEMP_ZAP zap
        join vcscheme.TEMP_PACIENT pac on pac.N_ZAP = zap.N_ZAP
        left join vcscheme.TEMP_PERS pers on cast(pers.ID_PAC as varchar(36)) = cast(pac.ID_PAC as varchar(36))
        left join #temp_dost   dost   on (CAST(dost.ID_PAC as varchar(36)) = cast(pac.ID_PAC as varchar(36)))
        left join #temp_dost_p dost_p on (CAST(dost_p.ID_PAC as varchar(36)) = cast(pac.ID_PAC as varchar(36)))

    SELECT '[IES].[T_SCHET_SLUCH_ACCOMPLISHED]' as tableName, ' информации о законченных случаях лечения' as operationName, (select count(*) from vcscheme.TEMP_Z_SLUCH) as tableCount

    SELECT SchetSluchAccomplishedID
            ,IDCASE
            ,USL_OK As [USL_OK]
            ,VIDPOM
            ,[FOR_POM] As [FOR_POM]
            ,NPR_MO
            ,NPR_DATE
            ,LPU
            ,DATE_Z_1
            ,DATE_Z_2
            ,KD_Z
            ,VNOV_M
            ,t1.RSLT AS [RSLT]
            ,[ISHOD] As [ISHOD]
            ,OS_SLUCH
            ,VB_P
            ,IDSP
            ,SUMV
            ,OPLATA
            ,isnull(case when @type = 693 then SUMV else SUMP end,0) as SUMP	
            ,isnull(case when @type = 693 then 0 else SANK_IT end,0) as SANK_IT
            ,t2.[SchetZapID] as [SchetZap]
            ,VBR
            ,P_OTK
            ,(select top 1 v017.IDDR from [IES].[T_V017_DISP_RESULT] v017 where t1.RSLT_D = v017.IDDR) as [RSLT_D]
            ,P_DISP2
    FROM vcscheme.TEMP_Z_SLUCH t1 
        JOIN vcscheme.TEMP_ZAP t2 on t2.N_ZAP = t1.N_ZAP

    SELECT '[IES].[T_SCHET_SLUCH]' as tableName, ' информации о случаях лечения' as operationName, (select count(*) from vcscheme.TEMP_SLUCH) as tableCount

    SELECT t1.[SchetSluchID]
            ,t1.[SL_ID]
            ,t1.[NHISTORY]
            ,t1.[DATE_1]
            ,t1.[DATE_2]
            ,t1.[DS0]
            ,t1.[DS1]
            ,t1.[CODE_MES1]
            ,t1.[CODE_MES2]
            ,t1.[ED_COL]
            ,t1.[TARIF] 
            ,t1.SUM_M as [SUMV]
            ,t1.SUM_M as [SUMP]
            ,t1.[COMENTSL]
            ,t1.[PROFIL]
            ,(select top 1 V020BedProfileId from IES.T_V020_BED_PROFILE where  t1.[PROFIL_K] = IDK_PR and DATEBEG<=t1.[DATE_1] and (DATEEND is null or DATEEND>=t1.[DATE_2]) ) as [PROFIL_K]
            ,(select top 1 V027CZabID from ies.T_V027_C_ZAB zab where IDCZ=t1.C_ZAB and DATEBEG<=t1.[DATE_1] and (DATEEND is null or DATEEND>=t1.[DATE_2])) as [C_ZAB]
            ,case when [VERS_SPEC] = 'V004' THEN [PRVS] ELSE null END as [PRVS]
            ,case when [VERS_SPEC] = 'V015' THEN [PRVS] ELSE null END as [PRVS2]
            ,case when [VERS_SPEC] = 'V021' or [VERS_SPEC] is null THEN (select top 1 IDSPEC from ies.T_V021_MED_SPEC v021 with(nolock) where CODE_SPEC=t1.PRVS and DATEBEG<=@ActualDate and (DATEEND is null or DATEEND>=@ActualDate)) ELSE null END as [PRVS3]
            ,t1.[DET] AS [DET]
            ,[PODR]
            ,[LPU_1]
            ,[IDDOKT]
            , 700 as [type_]
            ,t2.[SchetZapID] as [SchetZap]
            ,case when isnull(USL_OK,0) != 3 then null else (select top 1 V025KpcID from IES.T_V025_KPC where IDPC = [P_CEL] and DATEBEG<=t1.[DATE_1] and (DATEEND is null or DATEEND>=t1.[DATE_2])) end	As [P_CEL]
            ,t1.[VID_HMP]
            ,t5.V019VmpMethodID as [METOD_HMP]
            ,t1.[KD]
            ,t1.WEI as [WEI]
            ,t1.[P_PER]
            ,t1.[TAL_D]
            ,t1.[TAL_NUM]
            ,t1.[TAL_P]
            ,t1.[DS1_PR]
            ,t1.[DN]
            ,t1.[REAB]
            ,t1.[PR_D_N]
            ,t3.[SchetSluchAccomplishedID] as [SchetSluchAccomplished]
            ,t1.[DS_ONK]
            ,t1.[VERS_SPEC] AS [VERS_SPEC]
    FROM vcscheme.TEMP_SLUCH t1    
        JOIN vcscheme.TEMP_ZAP t2 on t2.N_ZAP = t1.N_ZAP
        JOIN vcscheme.TEMP_Z_SLUCH t3 on t1.IDCASE = t3.IDCASE
        outer apply 
        (
            select top 1 V019VmpMethodId
            from ies.T_V019_VMP_METHOD t100 with(nolock)
                left join ies.T_V022_MODPAC t200 with(nolock) on t200.V022ModpacID = t100.V022Modpac
            where t100.idhm = t1.METOD_HMP
                    and 
                t100.DATEBEG<=DATE_Z_2 
                    and 
                (t100.DATEEND is null or t100.DATEEND>=DATE_Z_2)
                    and 
                (t100.V022Modpac is null)
        ) t5

    drop table #temp_dost   
    drop table #temp_dost_p

    DROP TABLE vcscheme.TEMP_SCHET
    DROP TABLE vcscheme.TEMP_ZGLV
    DROP TABLE vcscheme.TEMP_ZAP
    DROP TABLE vcscheme.TEMP_PACIENT
    --DROP TABLE vcscheme.TEMP_PERS_ZGLV
    --DROP TABLE vcscheme.TEMP_PERS
    DROP TABLE vcscheme.TEMP_Z_SLUCH
    DROP TABLE vcscheme.TEMP_SLUCH
    DROP TABLE vcscheme.TEMP_USL
    DROP TABLE vcscheme.TEMP_SANK
    DROP TABLE vcscheme.TEMP_SANK_SL_ID
    DROP TABLE vcscheme.TEMP_CODE_EXP
    DROP TABLE vcscheme.TEMP_KSG_KPG
    DROP TABLE vcscheme.TEMP_CRIT
    DROP TABLE vcscheme.TEMP_SL_KOEF
    DROP TABLE vcscheme.TEMP_DS2_N
    DROP TABLE vcscheme.TEMP_DS2
    DROP TABLE vcscheme.TEMP_DS3
    DROP TABLE vcscheme.TEMP_CONS
    DROP TABLE vcscheme.TEMP_NAZ
    DROP TABLE vcscheme.TEMP_NAPR
    DROP TABLE vcscheme.TEMP_ONK_SL
    DROP TABLE vcscheme.TEMP_B_DIAG
    DROP TABLE vcscheme.TEMP_B_PROT
    DROP TABLE vcscheme.TEMP_ONK_USL
    DROP TABLE vcscheme.TEMP_LEK_PR_DATE_INJ

END
GO

