SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_SchetBulkLoad_karelia]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_SchetBulkLoad_karelia]
GO
-- Author:      Vitacore (с)
-- Create date: 2019-12-19
-- Description: Загрузка счета
-- =============================================
CREATE PROCEDURE [dbo].[sp_SchetBulkLoad_karelia]
	-- Add the parameters for the stored procedure here
	@FileID UNIQUEIDENTIFIER
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	PRINT 'Обновление идентификаторов' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

	UPDATE IDB.TEMP_ZGLV
	SET ZglvID = newid()

	UPDATE IDB.TEMP_SCHET
	SET SchetID = newid()

	UPDATE IDB.TEMP_ZAP
	SET ZapID = newid()

	UPDATE IDB.TEMP_PACIENT
	SET PacientID = newid()

	UPDATE IDB.TEMP_Z_SL
	SET ZSlID = newid()

	UPDATE IDB.TEMP_VNOV_M
	SET VnovMID = newid()

	UPDATE IDB.TEMP_OS_SLUCH
	SET OsSluchID = newid()

	UPDATE IDB.TEMP_SL
	SET SlID = newid()

	UPDATE IDB.TEMP_DS2
	SET Ds2ID = newid()

	UPDATE IDB.TEMP_DS3
	SET Ds3ID = newid()

	UPDATE IDB.TEMP_CODE_MES1
	SET CodeMes1ID = newid()

	UPDATE IDB.TEMP_DS2_N
	SET Ds2NID = newid()

	UPDATE IDB.TEMP_SANK
	SET SankID = newid()

	UPDATE IDB.TEMP_SANK_SL_ID
	SET SankSlIdID = newid()

	UPDATE IDB.TEMP_CODE_EXP
	SET CodeExpID = newid()

	UPDATE IDB.TEMP_DS2_N
	SET Ds2NID = newid()

	UPDATE IDB.TEMP_NAZ
	SET NazID = newid()

	UPDATE IDB.TEMP_NAPR
	SET NaprID = newid()

	UPDATE IDB.TEMP_CONS
	SET ConsID = newid()

	--update IDB.TEMP_ONK_SL set OnkSlID = newid() --автогенерация ID
	UPDATE IDB.TEMP_KSG_KPG
	SET KsgKpgID = newid()

	UPDATE IDB.TEMP_SL_LEK_PR
	SET SlLekPrID = newid()

	UPDATE IDB.TEMP_USL
	SET UslID = newid()

	UPDATE IDB.TEMP_B_DIAG
	SET BDiagID = newid()

	UPDATE IDB.TEMP_B_PROT
	SET BProtID = newid()

	--UPDATE IDB.TEMP_ONK_USL SET OnkUslID = newid() --автогенерация ID
	--update IDB.TEMP_ONK_USL_LEK_PR set LekPrID = newid() --автогенерация ID
	UPDATE IDB.TEMP_CRIT
	SET CritID = newid()

	UPDATE IDB.TEMP_SL_KOEF
	SET SlKoefID = newid()

	UPDATE IDB.TEMP_MR_USL_N
	SET MrUslNID = newid()

	UPDATE IDB.TEMP_MED_DEV
	SET MedDevID = newid()

	UPDATE IDB.TEMP_PERS
	SET PersID = newid()

	UPDATE IDB.TEMP_DOST
	SET DostID = newid()

	UPDATE IDB.TEMP_DOST_P
	SET DostPID = newid()

	--update IDB.TEMP_LEK_DOSE set LekDoseID = newid() --таблица не создаётся, т.к. связь 1 к 1
	PRINT 'Удаление счетов курсором' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)
	 
	DECLARE cur_SchRec CURSOR
	FOR
	SELECT t1.SchetID
	FROM IDB.TEMP_SCHET s
	join IDB.TEMP_ZGLV z on 1 = 1
	JOIN [ISZL].[T_SCHET] t1 ON s.NSCHET = t1.NSCHET
		AND s.[YEAR] = t1.[YEAR]
		AND s.[MONTH] = t1.[MONTH]
		--and s.[DSCHET] = t1.[DSCHET]
		AND s.[CODE_MO] = t1.[CODE_MO]
		AND s.[CODE] = t1.[CODE]		
		AND isnull(z.[FILENAME],convert(varchar(36), newid())) = isnull(t1.[FILENAME],convert(varchar(36), newid()))
	GROUP BY t1.SchetID

	OPEN cur_SchRec

	DECLARE @SchID UNIQUEIDENTIFIER

	FETCH NEXT
	FROM cur_SchRec
	INTO @SchID

	WHILE @@FETCH_STATUS = 0
	BEGIN
		EXEC [dbo].[sp_SchetDelete] @SchetID = @SchID

		FETCH NEXT
		FROM cur_SchRec
		INTO @SchID
	END

	CLOSE cur_SchRec

	DEALLOCATE cur_SchRec

	PRINT 'INSERT SCHET' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

	--грузим SCHET
	INSERT INTO [ISZL].[T_SCHET] (
		[SchetID]
		,[VERSION]
		,[DATA]
		,[YEAR]
		,[MONTH]
		,[NSCHET]
		,[DSCHET]
		,[ReceivedDate]
		,[FILENAME]
		,[CODE_MO]
		,[PLAT]
		,[SchetFile]
		,[CODE]
		,[DISP]
		)
	SELECT s.SchetID
		,[VERSION]
		,[DATA]
		,[YEAR]
		,[MONTH]
		,[NSCHET]
		,[DSCHET]
		,getdate()
		,[FILENAME]
		,[CODE_MO]
		,[PLAT]
		,@FileID
		,[CODE]
		,[DISP]
	FROM IDB.TEMP_SCHET s
	JOIN IDB.TEMP_ZGLV z ON 1 = 1

	PRINT 'Создание переменных по SCHET' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

	DECLARE @SchetID UNIQUEIDENTIFIER
	DECLARE @CodeMo VARCHAR(6)
	DECLARE @IsDisp BIT

	SELECT TOP 1 @SchetID = SchetID
		,@CodeMo = CODE_MO
		,@IsDisp = CASE 
			WHEN z.FILENAME LIKE 'D%'
				THEN 1
			ELSE 0
			END
	FROM IDB.TEMP_SCHET s
	JOIN IDB.TEMP_ZGLV z ON 1 = 1

	-- Проверка на существование @CodeMo в F003
	IF NOT EXISTS (SELECT 1 FROM ISZL.T_F003 f WHERE f.MCOD = @CodeMo)
		RAISERROR('IDB.TEMP_SCHET.CODE_MO not exists in ISZL.T_F003: %s', 16, 1, @CodeMo);

	PRINT 'CREATE INDEX ZAP, PACIENT, PERS' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

	CREATE INDEX IX_TEMP_ZAP_N_ZAP ON IDB.TEMP_ZAP (N_ZAP)

	CREATE INDEX IX_TEMP_PACIENT_N_ZAP ON IDB.TEMP_PACIENT (N_ZAP)

	CREATE INDEX IX_TEMP_PACIENT_ID_PAC ON IDB.TEMP_PACIENT (ID_PAC)

	CREATE INDEX IX_TEMP_PERS_ID_PAC ON IDB.TEMP_PERS (ID_PAC)

	PRINT 'INSERT ZAP' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

	INSERT INTO [ISZL].[T_SCHET_ZAP] (
		[SchetZapID]
		,[SPOLIS]
		,[NPOLIS]
		,[FAM]
		,[IM]
		,[OT]
		,[DR]
		,[DOCSER]
		,[DOCNUM]
		,[SNILS]
		,[VPOLIS]
		,[W]
		,[DOCTYPE]
		,[Schet]
		,[InsuredPolicyRecord]
		,[F002smo]
		,[DOCDATE]
		,[DOCORG]
		,[ID_PAC]
		,[ENP]
		,[TEL]
		,[Insured]
		,[N_ZAP]
		,[INV]
		,[SS_DOCTOR]
		)
	SELECT z.ZapID
		,p.SPOLIS AS [SPOLIS]
		,isnull(p.NPOLIS, p.ENP) AS [NPOLIS]
		,p.[FAM] AS [FAM]
		,p.[IM] AS [IM]
		,p.[OT] AS [OT]
		,p.[DR] AS [DR]
		,p.[DOCSER] AS [DOCSER]
		,p.[DOCNUM] AS [DOCNUM]
		,p.[SNILS] AS [SNILS]
		,p.[VPOLIS] AS [VPOLIS]
		,p.[W] AS [W]
		,p.[DOCTYPE] AS [DOCTYPE]
		,@SchetID AS [Schet]
		,NULL AS [InsuredPolicyRecord]
		,p.SMO AS [F002smo]
		,p.[DOCDATE] AS [DOCDATE]
		,p.[DOCORG] AS [DOCORG]
		,p.[ID_PAC] AS [ID_PAC]
		,p.[ENP] AS [ENP]
		,p.[TEL] AS [TEL]
		,NULL AS [Insured]
		,z.[N_ZAP] AS [N_ZAP]
		,p.[INV] AS [INV]
		,p.[SS_DOCTOR] as [SS_DOCTOR]
	--select *
	FROM IDB.TEMP_ZAP z
	CROSS APPLY (
		SELECT TOP 1 t101.[ID_PAC]
			,t101.[VPOLIS]
			,t101.[SPOLIS]
			,t101.[NPOLIS]
			,t101.[ENP]
			,t101.[ST_OKATO]
			,t101.[SMO]
			,t101.[SMO_OGRN]
			,t101.[SMO_OK]
			,t101.[SMO_NAM]
			,t101.[INV]
			,t101.[MSE]
			,t101.[NOVOR]
			,t101.[VNOV_D]
			,t100.[FAM]
			,t100.[IM]
			,t100.[OT]
			,t100.[W]
			,t100.[DR]
			,t100.[TEL]
			,t100.[FAM_P]
			,t100.[IM_P]
			,t100.[OT_P]
			,t100.[W_P]
			,t100.[DR_P]
			,t100.[MR]
			,t100.[DOCTYPE]
			,t100.[DOCSER]
			,t100.[DOCNUM]
			,t100.[DOCDATE]
			,t100.[DOCORG]
			,t100.[SNILS]
			,t100.[OKATOG]
			,t100.[OKATOP]
			,t100.[COMENTP]
		FROM IDB.TEMP_PERS t100
		JOIN IDB.TEMP_PACIENT t101 ON t100.ID_PAC = t101.ID_PAC
		WHERE t101.N_ZAP = z.N_ZAP
		) AS p

	PRINT 'CREATE INDEX Z_SL' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

	CREATE INDEX IX_TEMP_Z_SL_N_ZAP ON IDB.TEMP_Z_SL (N_ZAP)

	CREATE INDEX IX_TEMP_Z_SL_IDCASE ON IDB.TEMP_Z_SL (IDCASE)

	PRINT 'INSERT Z_SL' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

	INSERT INTO [ISZL].[T_SCHET_SLUCH_ACCOMPLISHED] (
		[SchetSluchAccomplishedID]
		,[IDCASE]
		,[USL_OK]
		,[DATE_Z_1]
		,[DATE_Z_2]
		,[RSLT]
		,[ISHOD]
		,[SchetZap]
		,[P_OTK]
		,[RSLT_D]
		,[MO]
		,[VIDPOM]
		,[FOR_POM]
		,[NPR_MO]
		,[NPR_DATE]
		,[NOM_NAP]
		,[VID_NAP_IS]
		,[NOM_NAP_IS]
		)
	SELECT zsl.ZSlID
		,zsl.IDCASE
		,zsl.USL_OK
		,zsl.DATE_Z_1
		,zsl.DATE_Z_2
		,zsl.RSLT
		,zsl.ISHOD
		,z.ZapID AS [SchetZap]
		,zsl.P_OTK
		,zsl.RSLT_D
		,@CodeMo AS MO
		,zsl.[VIDPOM]
		,zsl.[FOR_POM]
		,zsl.[NPR_MO]
		,zsl.[NPR_DATE]
		,zsl.[NOM_NAP]
		,zsl.[VID_NAP_IS]
		,zsl.[NOM_NAP_IS]
	FROM IDB.TEMP_ZAP z
	JOIN IDB.TEMP_Z_SL zsl ON zsl.N_ZAP = z.N_ZAP

	PRINT 'INSERT VNOV_M' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)
	PRINT 'INSERT OS_SLUCH' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)
	PRINT 'CREATE INDEX SL' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

	CREATE INDEX IX_TEMP_SL_N_ZAP ON IDB.TEMP_SL (N_ZAP)

	CREATE INDEX IX_TEMP_SL_IDCASE ON IDB.TEMP_SL (IDCASE)

	CREATE INDEX IX_TEMP_SL_SL_ID ON IDB.TEMP_SL (SL_ID)

	PRINT 'INSERT SL' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

	INSERT INTO [ISZL].[T_SCHET_SLUCH] (
		[SchetSluchID]
		,[IDCASE]
		,[SL_ID]
		,[NHISTORY]
		,[DATE_1]
		,[DATE_2]
		,[DS1]
		,[LPU]
		,[RSLT]
		,[ISHOD]
		,[SchetZap]
		,[Mkb]
		,[SchetSluchAccomplished]
		,[PROFIL_K]
		,[DN]
		,[RSLT_D]
		,[DISP]
		,[C_ZAB]
		,[PRVS3]
		,[DSONK]
		,[CODE_MES1]
		,[CODE_MES2]
		,[VID_HMP]
		,[METOD_HMP]
		,[P_CEL]
		,[IDDOKT]
		)
	SELECT sl.SlID
		,sl.IDCASE
		,sl.SL_ID
		,sl.NHISTORY
		,sl.DATE_1
		,sl.DATE_2
		,sl.DS1
		,@CodeMo AS [LPU]
		,zsl.RSLT
		,zsl.ISHOD
		,z.ZapID AS [SchetZap]
		,(
			SELECT TOP 1 m.MkbID
			FROM ISZL.T_MKB m
			WHERE m.MCBCode = sl.[DS1]
			) AS [Mkb]
		,zsl.ZSlID
		,sl.PROFIL_K
		,isnull(sl.DN, sl.PR_D_N)
		,zsl.RSLT_D
		,@IsDisp AS [DISP]
		,sl.[C_ZAB]
		,sl.[PRVS]
		,isnull(sl.[DS_ONK], 0)
		,(
			SELECT TOP 1 cm.CODE_MES1
			FROM IDB.TEMP_CODE_MES1 cm
			WHERE cm.IDCASE = sl.IDCASE
				AND cm.SL_ID = sl.SL_ID
			) AS [CODE_MES1]
		,sl.[CODE_MES2]
		,sl.[VID_HMP]
		,sl.[METOD_HMP]
		,sl.[P_CEL]
		,sl.[IDDOKT]
	FROM IDB.TEMP_ZAP z
	JOIN IDB.TEMP_Z_SL zsl ON zsl.N_ZAP = z.N_ZAP
	JOIN IDB.TEMP_SL sl ON zsl.N_ZAP = sl.N_ZAP
		AND zsl.IDCASE = sl.IDCASE

	PRINT 'INSERT DS2' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

	INSERT INTO [ISZL].[T_SCHET_SLUCH_DS] (
		[SchetSluchDsID]
		,[MKB]
		,[DS2_PR]
		,[SchetSluch]
		,[MKBType]
		,[PR_DS2_N]
		)
	SELECT ds2.Ds2ID
		,ds2.DS2
		,NULL AS [DS2_PR]
		,sl.SlID AS [SchetSluchID]
		,0 AS [MKBType] --сопуствующие диагнозы
		,NULL AS [PR_DS2_N]
	FROM IDB.TEMP_DS2 ds2
	JOIN IDB.TEMP_SL sl ON ds2.N_ZAP = sl.N_ZAP
		AND ds2.IDCASE = sl.IDCASE
		AND ds2.SL_ID = sl.SL_ID

	PRINT 'INSERT DS3' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

	INSERT INTO [ISZL].[T_SCHET_SLUCH_DS] (
		[SchetSluchDsID]
		,[MKB]
		,[DS2_PR]
		,[SchetSluch]
		,[MKBType]
		,[PR_DS2_N]
		)
	SELECT ds3.Ds3ID
		,ds3.DS3
		,NULL AS [DS2_PR]
		,sl.SlID AS [SchetSluchID]
		,1 AS [MKBType] --диагнозы осложнения
		,NULL AS [PR_DS2_N]
	FROM IDB.TEMP_DS3 ds3
	JOIN IDB.TEMP_SL sl ON ds3.N_ZAP = sl.N_ZAP
		AND ds3.IDCASE = sl.IDCASE
		AND ds3.SL_ID = sl.SL_ID

	PRINT 'INSERT DS3' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

	INSERT INTO [ISZL].[T_SCHET_SLUCH_DS] (
		[SchetSluchDsID]
		,[MKB]
		,[DS2_PR]
		,[SchetSluch]
		,[MKBType]
		,[PR_DS2_N]
		)
	SELECT ds2n.Ds2NID
		,ds2n.DS2
		,ds2n.DS2_PR AS [DS2_PR]
		,sl.SlID AS [SchetSluchID]
		,0 AS [MKBType] --сопуствующие диагнозы
		,ds2n.PR_DS2_N AS [PR_DS2_N]
	FROM IDB.TEMP_DS2_N ds2n
	JOIN IDB.TEMP_SL sl ON ds2n.N_ZAP = sl.N_ZAP
		AND ds2n.IDCASE = sl.IDCASE
		AND ds2n.SL_ID = sl.SL_ID

	PRINT 'INSERT CODE_MES1' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)
	PRINT 'CREATE INDEX ONK_SL' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

	CREATE INDEX IX_TEMP_ONK_SL_N_ZAP ON IDB.TEMP_ONK_SL (N_ZAP)

	CREATE INDEX IX_TEMP_ONK_SL_IDCASE ON IDB.TEMP_ONK_SL (IDCASE)

	CREATE INDEX IX_TEMP_ONK_SL_SL_ID ON IDB.TEMP_ONK_SL (SL_ID)

	PRINT 'INSERT ONK_SL' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

	INSERT INTO [ISZL].[T_SCHET_SLUCH_ONK] (
		[SchetSluchOnkID]
		,[WEI]
		,[K_FR]
		,[SOD]
		,[Diagnosis]
		,[STAD]
		,[DS1_T]
		,[ONK_T]
		,[ONK_N]
		,[ONK_M]
		,[MTSTZ]
		,[SchetSluch]
		,[HEI]
		,[BSA]
		)
	SELECT onksl.OnkSlID
		,onksl.[WEI]
		,onksl.[K_FR]
		,onksl.[SOD]
		,(
			SELECT TOP 1 m.Name
			FROM ISZL.T_MKB m
			WHERE m.MCBCode = sl.DS1
			)
		,onksl.[STAD]
		,onksl.[DS1_T]
		,onksl.[ONK_T]
		,onksl.[ONK_N]
		,onksl.[ONK_M]
		,isnull(onksl.[MTSTZ], 0)
		,sl.SlID
		,onksl.[HEI]
		,onksl.[BSA]
	FROM IDB.TEMP_ONK_SL onksl
	JOIN IDB.TEMP_SL sl
		ON onksl.N_ZAP = sl.N_ZAP
		AND onksl.IDCASE = sl.IDCASE
		AND onksl.SL_ID = sl.SL_ID
	GROUP BY
		onksl.OnkSlID
		,onksl.WEI
		,onksl.K_FR
		,onksl.SOD
		,sl.DS1
		,onksl.STAD
		,onksl.DS1_T
		,onksl.ONK_T
		,onksl.ONK_N
		,onksl.ONK_M
		,isnull(onksl.MTSTZ, 0)
		,sl.SlID
		,onksl.HEI
		,onksl.BSA;

	PRINT 'INSERT B_DIAG' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

	INSERT INTO [ISZL].[T_SCHET_SLUCH_ONK_DIAG] (
		[SchetSluchOnkDiagID]
		,[DIAG_DATE]
		,[DIAG_TIP]
		,[DIAG_CODE]
		,[DIAG_RSLT]
		,[REC_RSLT]
		,[SchetSluchOnk]
		)
	SELECT bd.BDiagID
		,bd.[DIAG_DATE]
		,bd.[DIAG_TIP]
		,bd.[DIAG_CODE]
		,bd.[DIAG_RSLT]
		,isnull(bd.[REC_RSLT], 0)
		,bd.OnkSlID
	FROM IDB.TEMP_B_DIAG bd
    JOIN IDB.TEMP_ONK_SL onksl on onksl.OnkSlID = bd.OnkSlID
	JOIN IDB.TEMP_SL sl ON onksl.N_ZAP = sl.N_ZAP
		AND onksl.IDCASE = sl.IDCASE
		AND onksl.SL_ID = sl.SL_ID
    where bd.DIAG_CODE is not null;

	PRINT 'INSERT B_PROT' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

	INSERT INTO [ISZL].[T_SCHET_SLUCH_ONK_PROT] (
		[SchetSluchOnkProtID]
		,[D_PROT]
		,[PROT]
		,[SchetSluchOnk]
		)
	SELECT bp.BProtID
		,bp.D_PROT
		,bp.[PROT]
		,bp.OnkSlID
	FROM IDB.TEMP_B_PROT bp
    JOIN IDB.TEMP_ONK_SL onksl on onksl.OnkSlID = bp.OnkSlID
	JOIN IDB.TEMP_SL sl ON onksl.N_ZAP = sl.N_ZAP
		AND onksl.IDCASE = sl.IDCASE
		AND onksl.SL_ID = sl.SL_ID
    where bp.PROT is not null;

	PRINT 'INSERT ONK_USL' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

	INSERT INTO [ISZL].[T_SCHET_USL_ONK] (
		[SchetUslOnkID]
		,[USL_TIP]
		,[HIR_TIP]
		,[LEK_TIP_L]
		,[LEK_TIP_V]
		,[PPTR]
		,[SchetSluchOnk]
		,[LUCH_TIP]
		)
	SELECT ou.OnkUslID
		,ou.[USL_TIP]
		,ou.[HIR_TIP]
		,ou.[LEK_TIP_L]
		,ou.[LEK_TIP_V]
		,isnull(ou.[PPTR], 0)
		,ou.OnkSlID
		,ou.[LUCH_TIP]
	FROM IDB.TEMP_ONK_USL ou
    JOIN IDB.TEMP_ONK_SL onksl on onksl.OnkSlID = ou.OnkSlID
	JOIN IDB.TEMP_SL sl ON onksl.N_ZAP = sl.N_ZAP
		AND onksl.IDCASE = sl.IDCASE
		AND onksl.SL_ID = sl.SL_ID
    where ou.USL_TIP is not null
    group by  
		ou.OnkUslID,
		ou.USL_TIP,
		ou.HIR_TIP,
		ou.LEK_TIP_L,
		ou.LEK_TIP_V,
		isnull(ou.PPTR, 0),
		ou.OnkSlID,
		ou.LUCH_TIP;

	PRINT 'INSERT ONK_USL_LEK_PR' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

	INSERT INTO [ISZL].T_SCHET_USL_ONK_LEK_PR (
		SchetUslOnkLekPrID
		,[SchetUslOnk]
		,[REGNUM]
		,[CODE_SH]
		)
	SELECT lp.OnkUslLekPrId
		,lp.OnkUslID
		,lp.REGNUM
		,lp.CODE_SH
	FROM IDB.TEMP_ONK_USL_LEK_PR lp
    JOIN IDB.TEMP_ONK_USL ou on ou.OnkUslID = lp.OnkUslID
	JOIN IDB.TEMP_ONK_SL onksl on onksl.OnkSlID = ou.OnkSlID
	JOIN IDB.TEMP_SL sl ON onksl.N_ZAP = sl.N_ZAP
		AND onksl.IDCASE = sl.IDCASE
		AND onksl.SL_ID = sl.SL_ID
    where CODE_SH is not null
	group by
		lp.OnkUslLekPrId,
		lp.OnkUslID,
		lp.REGNUM,
		lp.CODE_SH;

	PRINT 'INSERT ONK_USL_LEK_PR' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

	INSERT INTO ISZL.T_SCHET_USL_ONK_LEK_PR_DATE (
		SchetUslOnkLekPrDateID
		,DATE_INJ
		,SchetUslOnkLekPr
		)
	SELECT newid()
		,lpdi.DATE_INJ
		,lpdi.OnkUslLekPrId
	FROM IDB.TEMP_DATE_INJ lpdi
    JOIN IDB.TEMP_ONK_USL_LEK_PR lp on lp.onkusllekprid = lpdi.onkusllekprid
	JOIN IDB.TEMP_ONK_USL ou on ou.OnkUslID = lp.OnkUslID
	JOIN IDB.TEMP_ONK_SL onksl on onksl.OnkSlID = ou.OnkSlID
	JOIN IDB.TEMP_SL sl ON onksl.N_ZAP = sl.N_ZAP
		AND onksl.IDCASE = sl.IDCASE
		AND onksl.SL_ID = sl.SL_ID
    where lpdi.DATE_INJ is not null
	group by
		lpdi.DATE_INJ,
		lpdi.OnkUslLekPrId;

	PRINT 'CREATE INDEX SANK' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

	CREATE INDEX IX_TEMP_SANK_N_ZAP ON IDB.TEMP_SANK (N_ZAP)

	CREATE INDEX IX_TEMP_SANK_IDCASE ON IDB.TEMP_SANK (IDCASE)

	CREATE INDEX IX_TEMP_SANK_S_CODE ON IDB.TEMP_SANK (S_CODE)

	CREATE INDEX IX_TEMP_SANK_S_SUM ON IDB.TEMP_SANK (S_SUM)

	CREATE INDEX IX_TEMP_SANK_SL_ID_N_ZAP ON IDB.TEMP_SANK_SL_ID (N_ZAP)

	CREATE INDEX IX_TEMP_SANK_SL_ID_IDCASE ON IDB.TEMP_SANK_SL_ID (IDCASE)

	CREATE INDEX IX_TEMP_SANK_SL_ID_S_CODE ON IDB.TEMP_SANK_SL_ID (S_CODE)

	CREATE INDEX IX_TEMP_SANK_SL_ID_S_SUM ON IDB.TEMP_SANK_SL_ID (S_SUM)

	PRINT 'INSERT SANK' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

	INSERT INTO [ISZL].[T_SCHET_SLUCH_SANK] (
		[SchetSluchSankID]
		,[S_CODE]
		,[SchetSluch]
		,[S_TIP]
		,[S_IST]
		,[SchetSluchAccomplished]
		,[DATE_ACT]
		,[NUM_ACT]
		,[S_OSN]
		)
	SELECT sank.SankID
		,sank.[S_CODE]
		,NULL AS [SchetSluch]
		,sank.[S_TIP]
		,sank.[S_IST]
		,zsl.ZSlID
		,sank.[DATE_ACT]
		,sank.[NUM_ACT]
		,sank.S_OSN
	FROM IDB.TEMP_SANK sank
	JOIN IDB.TEMP_Z_SL zsl ON sank.N_ZAP = zsl.N_ZAP
		AND sank.IDCASE = zsl.IDCASE
	WHERE NOT EXISTS (
			SELECT TOP 1 t100.SlID
			FROM IDB.TEMP_SL t100
			JOIN IDB.TEMP_SANK_SL_ID t101 ON t100.N_ZAP = t101.N_ZAP
				AND t100.IDCASE = t101.IDCASE
				AND t100.SL_ID = t101.SL_ID
			WHERE t101.N_ZAP = sank.N_ZAP
				AND t101.IDCASE = sank.IDCASE
				AND t101.S_CODE = sank.S_CODE
				AND isnull(t101.S_SUM, 0) = isnull(sank.S_SUM, 0)
			)

	INSERT INTO [ISZL].[T_SCHET_SLUCH_SANK] (
		[SchetSluchSankID]
		,[S_CODE]
		,[SchetSluch]
		,[S_TIP]
		,[S_IST]
		,[SchetSluchAccomplished]
		,[DATE_ACT]
		,[NUM_ACT]
		,[S_OSN]
		)
	SELECT sank.SankID
		,sank.[S_CODE]
		,sl.SlID AS [SchetSluch]
		,sank.[S_TIP]
		,sank.[S_IST]
		,zsl.ZSlID
		,sank.[DATE_ACT]
		,sank.[NUM_ACT]
		,sank.S_OSN
	FROM IDB.TEMP_SANK sank
	JOIN IDB.TEMP_Z_SL zsl ON sank.N_ZAP = zsl.N_ZAP
		AND sank.IDCASE = zsl.IDCASE
	JOIN IDB.TEMP_SANK_SL_ID ssi ON ssi.N_ZAP = sank.N_ZAP
		AND ssi.IDCASE = sank.IDCASE
		AND ssi.S_CODE = sank.S_CODE
		AND isnull(ssi.S_SUM, 0) = isnull(sank.S_SUM, 0)
	JOIN IDB.TEMP_SL sl ON sl.N_ZAP = ssi.N_ZAP
		AND sl.IDCASE = ssi.IDCASE
		AND sl.SL_ID = ssi.SL_ID

	PRINT 'CREATE INDEX NAPR' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

	CREATE INDEX IX_TEMP_NAPR_N_ZAP ON IDB.TEMP_NAPR (N_ZAP)

	CREATE INDEX IX_TEMP_NAPR_IDCASE ON IDB.TEMP_NAPR (IDCASE)

	CREATE INDEX IX_TEMP_NAPR_SL_ID ON IDB.TEMP_NAPR (SL_ID)

	PRINT 'INSERT NAPR' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

	INSERT INTO [ISZL].[T_SCHET_USL_NAPR] (
		[SchetUslNaprID]
		,[NAPR_DATE]
		,[NAPR_MO]
		,[NAPR_V]
		,[MET_ISSL]
		,[NAPR_USL]
		,[SchetSluch]
		)
	SELECT napr.NaprID
		,napr.[NAPR_DATE]
		,napr.[NAPR_MO]
		,napr.[NAPR_V]
		,napr.[MET_ISSL]
		,napr.[NAPR_USL]
		,s.SlID
	FROM IDB.TEMP_NAPR napr
	JOIN IDB.TEMP_SL s ON napr.N_ZAP = s.N_ZAP
		AND napr.IDCASE = s.IDCASE
		AND napr.SL_ID = s.SL_ID

	PRINT 'CREATE INDEX CONS' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

	CREATE INDEX IX_TEMP_CONS_N_ZAP ON IDB.TEMP_CONS (N_ZAP)

	CREATE INDEX IX_TEMP_CONS_IDCASE ON IDB.TEMP_CONS (IDCASE)

	CREATE INDEX IX_TEMP_CONS_SL_ID ON IDB.TEMP_CONS (SL_ID)

	PRINT 'INSERT CONS' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

	INSERT INTO [ISZL].[T_SCHET_SLUCH_CONS] (
		[SchetSluchConsID]
		,[SchetSluch]
		,[DT_CONS]
		,[PR_CONS]
		)
	SELECT cons.ConsID
		,s.SlID
		,cons.DT_CONS
		,cons.PR_CONS
	FROM IDB.TEMP_CONS cons
	JOIN IDB.TEMP_SL s ON cons.N_ZAP = s.N_ZAP
		AND cons.IDCASE = s.IDCASE
		AND cons.SL_ID = s.SL_ID

	PRINT 'CREATE INDEX USL' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

	CREATE INDEX IX_TEMP_USL_N_ZAP ON IDB.TEMP_USL (N_ZAP)

	CREATE INDEX IX_TEMP_USL_IDCASE ON IDB.TEMP_USL (IDCASE)

	CREATE INDEX IX_TEMP_USL_SL_ID ON IDB.TEMP_USL (SL_ID)

	CREATE INDEX IX_TEMP_USL_IDSERV ON IDB.TEMP_USL (IDSERV)

	PRINT 'INSERT USL' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

	INSERT INTO [ISZL].[T_SCHET_USL] (
		[SchetUslID]
		,[IDSERV]
		,[LPU]
		,[DATE_IN]
		,[DATE_OUT]
		,[DS]
		,[KOL_USL]
		,[VID_VME]
		,[SchetSluch]
		,[CODE_USL]
		,[P_OTK]
		,[CODE_MD]
		)
	SELECT u.UslID
		,u.IDSERV
		,u.LPU
		,u.DATE_IN
		,u.DATE_OUT
		,u.DS
		,u.KOL_USL
		,u.VID_VME
		,s.SlID
		,u.CODE_USL
		,u.P_OTK
		,u.[CODE_MD]
	FROM IDB.TEMP_USL u
	JOIN IDB.TEMP_SL s ON u.N_ZAP = s.N_ZAP
		AND u.IDCASE = s.IDCASE
		AND u.SL_ID = s.SL_ID

	PRINT 'CREATE INDEX NAZ' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

	CREATE INDEX IX_TEMP_NAZ_N_ZAP ON IDB.TEMP_NAZ (N_ZAP)

	CREATE INDEX IX_TEMP_NAZ_IDCASE ON IDB.TEMP_NAZ (IDCASE)

	CREATE INDEX IX_TEMP_NAZ_SL_ID ON IDB.TEMP_NAZ (SL_ID)

	PRINT 'INSERT NAZ' + ' ' + convert(VARCHAR(10), getdate(), 104) + ' ' + convert(VARCHAR(8), getdate(), 114)

	INSERT INTO [ISZL].[T_SCHET_SLUCH_NAZ] (
		[SchetSluchNazID]
		,[NAZ_N]
		,[NAZR]
		,[NAZ_SP]
		,[NAZ_V]
		,[NAZ_PK]
		,[SchetSluch]
		,[NAZ_USL]
		,[NAPR_DATE]
		,[NAPR_MO]
		)
	SELECT n.NazID
		,n.NAZ_N
		,n.NAZ_R
		,n.NAZ_SP
		,n.NAZ_V
		,n.NAZ_PK
		,s.SlID
		,n.NAZ_USL
		,n.NAPR_DATE
		,n.NAPR_MO
	FROM IDB.TEMP_NAZ n
	JOIN IDB.TEMP_SL s ON n.N_ZAP = s.N_ZAP
		AND n.IDCASE = s.IDCASE
		AND n.SL_ID = s.SL_ID

	DROP TABLE IDB.TEMP_ZGLV

	DROP TABLE IDB.TEMP_SCHET

	DROP TABLE IDB.TEMP_ZAP

	DROP TABLE IDB.TEMP_PACIENT

	DROP TABLE IDB.TEMP_Z_SL

	DROP TABLE IDB.TEMP_VNOV_M

	DROP TABLE IDB.TEMP_OS_SLUCH

	DROP TABLE IDB.TEMP_SL

	DROP TABLE IDB.TEMP_DS2

	DROP TABLE IDB.TEMP_DS3

	DROP TABLE IDB.TEMP_CODE_MES1

	DROP TABLE IDB.TEMP_SANK

	DROP TABLE IDB.TEMP_SANK_SL_ID

	DROP TABLE IDB.TEMP_CODE_EXP

	DROP TABLE IDB.TEMP_DS2_N

	DROP TABLE IDB.TEMP_NAZ

	DROP TABLE IDB.TEMP_NAPR

	DROP TABLE IDB.TEMP_CONS

	DROP TABLE IDB.TEMP_ONK_SL

	DROP TABLE IDB.TEMP_KSG_KPG

	DROP TABLE IDB.TEMP_CRIT

	DROP TABLE IDB.TEMP_SL_LEK_PR

	DROP TABLE IDB.TEMP_USL

	DROP TABLE IDB.TEMP_B_DIAG

	DROP TABLE IDB.TEMP_B_PROT

	DROP TABLE IDB.TEMP_ONK_USL

	DROP TABLE IDB.TEMP_ONK_USL_LEK_PR

	DROP TABLE IDB.TEMP_DATE_INJ

	DROP TABLE IDB.TEMP_SL_KOEF

	DROP TABLE IDB.TEMP_MR_USL_N

	DROP TABLE IDB.TEMP_MED_DEV

	DROP TABLE IDB.TEMP_ZGLV_PERS

	DROP TABLE IDB.TEMP_PERS

	DROP TABLE IDB.TEMP_DOST

	DROP TABLE IDB.TEMP_DOST_P
END