if exists ( select * from sys.all_objects ao where ao.name = 'sp_LoadPpList' ) DROP PROCEDURE [dbo].[sp_LoadPpList]
GO

CREATE PROCEDURE [dbo].[sp_LoadPpList] @x xml
 ,@FileID uniqueidentifier = null
 ,@Worker uniqueidentifier = null
 ,@PackageName varchar(100)
 ,@FileName varchar(100)
 --PREREAD секция
 ,@ID varchar(36)
 ,@MCOD varchar(6)

AS
BEGIN
	set nocount on;
	declare @flkFileName varchar(100) = 'FLK_' + @FileName + '.xml'
	DECLARE @RaisErrorMsg VARCHAR(2047) = ''

	IF EXISTS (
			SELECT *
			FROM tempdb.dbo.sysobjects o
			WHERE o.xtype IN ('U')
				AND o.id = object_id(N'tempdb..#ZGLV')
			)
		DROP TABLE #ZGLV;

	SELECT @FileID AS [FileId]
		,T.c.value('./VERSION[1]', 'varchar(5)') AS [VERSION]
		,T.c.value('./DATA[1]', 'datetime') AS [DATA]
		,T.c.value('./ID[1]', 'varchar(50)') AS ID
		,T.c.value('./MCOD[1]', 'varchar(6)') AS [MCOD]
		,CAST(CAST(CAST(GETDATE() AS FLOAT) AS INT) AS DATETIME) AS [ReceivedDate]
		,GETDATE() AS [ReceivedTime]
	INTO #ZGLV
	FROM @x.nodes('/PP_LIST/ZGLV') T(c)

	SET @RaisErrorMsg += 'fileid: ' + isnull(convert(VARCHAR(36), @fileid), 'null') + CHAR(13)

	IF EXISTS (
			SELECT *
			FROM tempdb.dbo.sysobjects o
			WHERE o.xtype IN ('U')
				AND o.id = object_id(N'tempdb..#ZL')
			)
		DROP TABLE #ZL;

	SELECT NewId() AS DispDirectFactId
		,T.c.value('./ID_PAC[1]', 'varchar(36)') AS [ID_PAC]
		,T.c.value('./FAM[1]', 'varchar(40)') AS [FAM]
		,T.c.value('./IM[1]', 'varchar(40)') AS [IM]
		,T.c.value('./OT[1]', 'varchar(40)') AS [OT]
		,T.c.value('./W[1]', 'int') AS [W]
		,T.c.value('./DR[1]', 'datetime') AS [DR]
		,T.c.value('./ENP[1]', 'varchar(16)') AS [ENP]
		,T.c.value('./VPOLIS[1]', 'int') AS [VPOLIS]
		,T.c.value('./SPOLIS[1]', 'varchar(10)') AS [SPOLIS]
		,T.c.value('./NPOLIS[1]', 'varchar(20)') AS [NPOLIS]
		,T.c.value('./SNILS[1]', 'varchar(14)') AS [SNILS]
		,T.c.value('./PP[1]/DATA_1[1]', 'date') AS [DATA_1]
		,T.c.value('./PP[1]/DATA_2[1]', 'date') AS [DATA_2]
		,T.c.value('./PP[1]/DISP[1]', 'varchar(3)') AS [DISP]
		,T.c.value('./PP[1]/P_OTK[1]', 'int') AS [P_OTK]
		,T.c.value('./PP[1]/RSLT_D[1]', 'int') AS [RSLT_D]
		,T.c.value('./PP[1]/NHISTORY[1]', 'varchar(50)') AS [NHISTORY]
		,T.c.value('./PP[1]/DS1[1]', 'varchar(10)') AS [DS1]
		,T.c.value('./PP[1]/DS1_PR[1]', 'int') AS [DS1_PR]
		,T.c.value('./PP[1]/DS_ONK[1]', 'int') AS [DS_ONK]
		,T.c.value('./PP[1]/PR_D_N[1]', 'int') AS [PR_D_N]
		,T.c.value('../ZGLV[1]/DATA[1]', 'datetime') AS [DATA]
		,T.c.value('../ZGLV[1]/MCOD[1]', 'varchar(6)') AS [CODE_MO]
		,T.c.value('./V016Dispt[1]', 'uniqueidentifier') AS V016Dispt --Заглушка
		,T.c.value('./InsuredPolicyRecordID[1]', 'varchar(36)') AS [InsuredPolicyRecordID] -- заглушка
		,convert(UNIQUEIDENTIFIER, NULL) AS DispDirectFactIdOld
		,convert(UNIQUEIDENTIFIER, NULL) AS Insured
	--,T.c.value('./DispDirectID[1]', 'uniqueidentifier') AS DispDirectID -- заглушка
	--,T.c.value('./DuplicateCount[1]', 'int') AS DuplicateCount -- заглушка
	INTO #ZL
	FROM @x.nodes('/PP_LIST/ZL') T(c)

	IF EXISTS (
			SELECT *
			FROM tempdb.dbo.sysobjects o
			WHERE o.xtype IN ('U')
				AND o.id = object_id(N'tempdb..#USL')
			)
		DROP TABLE #USL;

	SELECT T.c.value('../../ID_PAC[1]', 'varchar(36)') AS ID_PAC
		,T.c.value('../../ENP[1]', 'varchar(16)') AS [ENP]
		,T.c.value('./DATE_IN[1]', 'date') AS [DATE_IN]
		,T.c.value('./DATE_OUT[1]', 'date') AS [DATE_OUT]
		,T.c.value('./P_OTK[1]', 'int') AS [P_OTK]
		,T.c.value('./CODE_USL[1]', 'varchar(20)') AS [CODE_USL]
		,T.c.value('./USL[1]', 'varchar(254)') AS [USL]
		,T.c.value('./PRVS[1]', 'int') AS [PRVS]
		,T.c.value('./CODE_MD[1]', 'varchar(25)') AS [CODE_MD]
	INTO #USL
	FROM @x.nodes('/PP_LIST/ZL/PP/USL') T(c)

	UPDATE p
	SET p.V016Dispt = v016.V016ID
	FROM #ZL p
	JOIN iszl.T_V016_DISPT v016 ON v016.IDDT = p.DISP
		AND v016.DATEEND IS NULL

	CREATE TABLE #Errors (
		ID_PAC varchar(36)
		,CODE INT
		,NAME varchar(255)
		,FIELD varchar(20)
		,NOTE VARCHAR(250)
		)

	--проверка что всё заполнено!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
	INSERT INTO #Errors(ID_PAC, CODE, NAME, FIELD, NOTE)
	SELECT 
		t1.ID_PAC
		,1
		,'Значение поля DISP не содержится в справочнике V016 ID_PAC(' + t1.ID_PAC + ') NPOLIS(' + t1.NPOLIS + ')'
		,'DISP'
		,''
	FROM #ZL t1
	WHERE t1.V016Dispt IS NULL
	
	--step 0
	--update t1
	--set t1.[InsuredPolicyRecordID] = (
	--  select top 1 t2.InsuredID from [ISZL].T_INSURED t2
	--  where (t2.ID_PAC = t1.ID_PAC or t2.ENP = t1.ENP)   
	-- )
	--from #ZL t1
	--where t1.INSURED is null
	--0. Ищем полис по номеру полиса. Не важно актуальный он или нет
	--step 0.
	UPDATE t1
	SET t1.[InsuredPolicyRecordID] = (
			SELECT TOP 1 InsuredPolicyRecordID
			FROM [ISZL].T_INSURED_POLICY_RECORD t2
			JOIN [ISZL].T_POLICY p ON p.PolicyID = t2.[Policy]
			WHERE (
					t2.LastName = t1.FAM
					AND t2.FirstName = t1.IM
					AND t2.Patronymic = t1.OT
					AND t2.Birthday = t1.DR
					AND (
						p.Number = t1.NPOLIS
						OR p.Number = t1.ENP
						)
					AND t2.DateStop IS NULL
					)
			ORDER BY t2.DateBegin DESC
			)
	FROM #ZL t1
	WHERE t1.[InsuredPolicyRecordID] IS NULL
		AND (
			SELECT COUNT(*)
			FROM #Errors
			) = 0

	--step 1. без отчества
	UPDATE t1
	SET t1.[InsuredPolicyRecordID] = (
			SELECT TOP 1 InsuredPolicyRecordID
			FROM [ISZL].T_INSURED_POLICY_RECORD t2
			JOIN [ISZL].T_POLICY p ON p.PolicyID = t2.[Policy]
			WHERE (
					t2.LastName = t1.FAM
					AND t2.FirstName = t1.IM
					AND t2.Birthday = t1.DR
					AND (
						p.Number = t1.NPOLIS
						OR p.Number = t1.ENP
						)
					AND t2.DateStop IS NULL
					)
			ORDER BY t2.DateBegin DESC
			)
	FROM #ZL t1
	WHERE t1.[InsuredPolicyRecordID] IS NULL
		AND (
			SELECT COUNT(*)
			FROM #Errors
			) = 0

	--step 2. без отчества и ДР
	UPDATE t1
	SET t1.[InsuredPolicyRecordID] = (
			SELECT TOP 1 InsuredPolicyRecordID
			FROM [ISZL].T_INSURED_POLICY_RECORD t2
			JOIN [ISZL].T_POLICY p ON p.PolicyID = t2.[Policy]
			WHERE (
					t2.LastName = t1.FAM
					AND t2.FirstName = t1.IM
					AND (
						p.Number = t1.NPOLIS
						OR p.Number = t1.ENP
						)
					AND t2.DateStop IS NULL
					)
			ORDER BY t2.DateBegin DESC
			)
	FROM #ZL t1
	WHERE t1.[InsuredPolicyRecordID] IS NULL
		AND (
			SELECT COUNT(*)
			FROM #Errors
			) = 0

	--step 3. без отчества и Фамилии
	UPDATE t1
	SET t1.[InsuredPolicyRecordID] = (
			SELECT TOP 1 InsuredPolicyRecordID
			FROM [ISZL].T_INSURED_POLICY_RECORD t2
			JOIN [ISZL].T_POLICY p ON p.PolicyID = t2.[Policy]
			WHERE (
					t2.FirstName = t1.IM
					AND t2.Birthday = t1.DR
					AND (
						p.Number = t1.NPOLIS
						OR p.Number = t1.ENP
						)
					AND t2.DateStop IS NULL
					)
			ORDER BY t2.DateBegin DESC
			)
	FROM #ZL t1
	WHERE t1.[InsuredPolicyRecordID] IS NULL
		AND (
			SELECT COUNT(*)
			FROM #Errors
			) = 0

	--Ищем актуальный полис застрахованного 
	--update t1
	--set t1.[ActualInsuredPolicyRecordID] = (
	--  select top 1 InsuredPolicyRecordID 
	--  from [ISZL].T_INSURED_POLICY_RECORD t2
	--	join [ISZL].T_POLICY p on p.PolicyID = t2.[Policy]
	--	--join [ISZL].T_F008_OMS_TYPE f008 on f008.IDDOC = p.OmsType
	--  where t2.Insured = t3.Insured and t2.DateBegin <= t1.[DATA] and (not(t2.DateStop is not null and t2.DateStop <= t1.[DATA]))
	--  order by t2.DateBegin, p.OmsType
	-- )
	--from #ZL t1
	--join [ISZL].T_INSURED_POLICY_RECORD t3 on t3.[InsuredPolicyRecordID] = t1.[InsuredPolicyRecordID]
	INSERT INTO #Errors(ID_PAC, CODE, NAME, FIELD, NOTE)
	SELECT 
		t1.ID_PAC
		, 2
		,'Не найден полис из учета полисов застрахованного ID_PAC(' + t1.ID_PAC + ') NPOLIS(' + t1.NPOLIS + ')'
		,'NPOLIS'
		,''
	FROM #ZL t1
	WHERE t1.[InsuredPolicyRecordID] IS NULL

	INSERT INTO #Errors(ID_PAC, CODE, NAME, FIELD, NOTE)
	SELECT
		null
		, 2
		,'FileId имеет значение null'
		,'ZGLV'
		,''
	FROM #ZGLV t1
	WHERE t1.FileId IS NULL

	INSERT INTO #Errors(ID_PAC, CODE, NAME, FIELD, NOTE)
	SELECT 
		t1.ID_PAC
		, 2
		,'Дубль ID_PAC' + t1.ID_PAC
		,'ID_PAC'
		,''
	FROM #ZL t1
	GROUP BY t1.ID_PAC
	having count(*) > 1

	if exists (
	select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#SaveFilesScheme')
	)
	DROP TABLE #SaveFilesScheme;

	create table #SaveFilesScheme
	(	
		id int,
		IsSaveFilesScheme int,
		SaveJobsCount int,
		Scheme varchar(20),
		IsSingleXmlFlk bit,
		Filename varchar(300),
		Content varchar(max),
		CodePage varchar(20),
		TargetDirectory varchar(20),
		ErrorDescrXPath varchar(100),
		Lexem varchar(500)
	)


	IF (
			SELECT COUNT(*)
			FROM #Errors
			) > 0
	BEGIN

	if exists (
			select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#errors_zap')
		)
		DROP TABLE #errors_zap;
			
		select distinct ID_PAC 
		into #errors_zap
		from #Errors 


	declare @flk_xml varchar(max) = (
		'<?xml version="1.0" encoding="utf-8"?>' + (
		SELECT
					t0.ID
					,t0.MCOD as MCOD
					,'ERROR' as RESULT
					,(
						SELECT 
						ID_PAC
						,(
							select 
							CODE
							, NAME
							, FIELD
							, NOTE
							FROM #Errors t2
							where t2.ID_PAC = t1.ID_PAC
							or (t2.ID_PAC is null and t1.ID_PAC is null)
							FOR
							XML PATH('ERRORS'),
							TYPE
						)
						FROM #errors_zap t1
						FOR
						XML PATH('ZL'),
						TYPE
					)
				from #ZGLV t0
				FOR XML PATH(''),
				ROOT('FLK_LIST')
		))
		insert into #SaveFilesScheme(Id, IsSingleXmlFlk, Filename, Content, CodePage, TargetDirectory, ErrorDescrXPath)
		select 0, 1, @flkFileName, @flk_xml, 'utf-8', 'FLK', '/FLK_LIST/ZL/ERRORS/NAME'
	END
	ELSE
	BEGIN
		PRINT 'set insured' + ' ' + convert(VARCHAR(8), getdate(), 114)

		UPDATE t0
		SET Insured = t1.Insured
		FROM #ZL t0
		JOIN ISZL.T_INSURED_POLICY_RECORD t1 ON t0.InsuredPolicyRecordID = t1.InsuredPolicyRecordID

		PRINT 'create file' + ' ' + convert(VARCHAR(8), getdate(), 114)

		INSERT INTO VCLib.T_FILE (
			[FileID]
			,[Name]
			,[CreateDateTime]
			,[FileNameForm]
			,[SaveFileLocation]
			,[UseOle]
			,[type_]
			)
		SELECT z.FileId
			,@FileName
			,GetDate()
			,1
			,1
			,0
			,(
				SELECT EntityID
				FROM VCLib.T_ENTITIES
				WHERE EntName = 'DISP_DIRECT_FILE'
				)
		FROM #ZGLV z

		INSERT INTO [ISZL].[T_DISP_DIRECT_FACT_FILE] (
			[FileID]
			,[VERSION]
			,[ReceivedDate]
			,[ReceivedTime]
			,[Worker]
			,[YEAR]
			,[MONTH]
			,[V016Dispt]
			,[FILENAME]
			,[DATA]
			)
		SELECT z.FileId
			,[VERSION]
			,GetDate()
			,GetDate()
			,@Worker
			,NULL AS [YEAR]
			,NULL AS [MONTH]
			,NULL AS [V016Dispt]
			,@FileName
			,z.DATA
		FROM #ZGLV z

		PRINT 'find existing disp direct fact' + ' ' + convert(VARCHAR(8), getdate(), 114)

		UPDATE t0
		SET DispDirectFactIdOld = t1.DispDirectFactID
		FROM #ZL t0
		JOIN [ISZL].[T_DISP_DIRECT_FACT] t1 ON t0.CODE_MO = t1.CODE_MO
			AND t0.DATA_1 = t1.DateBegin
			AND t0.V016Dispt = t1.V016Dispt
			AND t0.Insured = t1.Insured

		PRINT 'update existing disp direct fact' + ' ' + convert(VARCHAR(8), getdate(), 114)

		UPDATE t0
		SET t0.DateEnd = t1.DATA_2
			,t0.P_OTK = t1.P_OTK
			,t0.RSLT_D = t1.RSLT_D
			,t0.NHISTORY = t1.NHISTORY
			,t0.DS1 = t1.DS1
			,t0.DS1_PR = t1.DS1_PR
			,t0.DS_ONK = t1.DS_ONK
			,t0.PR_D_N = t1.PR_D_N
		FROM [ISZL].[T_DISP_DIRECT_FACT] t0
		JOIN #ZL t1 ON t0.DispDirectFactID = t1.DispDirectFactIdOld

		PRINT 'delete existing disp direct fact usls' + ' ' + convert(VARCHAR(8), getdate(), 114)

		DELETE t0
		FROM ISZL.T_DISP_DIRECT_FACT_USL t0
		JOIN #ZL t1 ON t0.DispDirectFact = t1.DispDirectFactIdOld

		PRINT 'чтобы вставить услуги скопом поменяем новый идентификатор на уже существующий' + ' ' + convert(VARCHAR(8), getdate(), 114)

		UPDATE #ZL
		SET DispDirectFactId = DispDirectFactIdOld
		WHERE DispDirectFactIdOld IS NOT NULL

		PRINT 'insert new [T_DISP_DIRECT_FACT]s' + ' ' + convert(VARCHAR(8), getdate(), 114)

		INSERT INTO [ISZL].[T_DISP_DIRECT_FACT] (
			[DispDirectFactID]
			,[DateBegin]
			,[DateEnd]
			,[FAM]
			,[IM]
			,[OT]
			,[RSLT_D]
			,[DispDirect]
			,[DR]
			,[ENP]
			,[CODE_MO]
			,[ISHOD]
			,[NSCHET]
			,[YEAR]
			,[MONTH]
			,[V016Dispt]
			,[DispDirectFactFile]
			,[Comments]
			,[PR_D_N]
			,[DS1]
			,[Insured]
			,[CreationDate]
			,[SchetSluch]
			,[VPOLIS]
			,[SPOLIS]
			,[NPOLIS]
			,[SNILS]
			,[P_OTK]
			,[NHISTORY]
			,[DS1_PR]
			,[DS_ONK]
			)
		SELECT t0.DispDirectFactId --<DispDirectFactID, uniqueidentifier,>
			,t0.DATA_1 --<DateBegin, datetime,>
			,t0.DATA_2 --<DateEnd, datetime,>
			,t0.FAM --<FAM, varchar(40),>
			,t0.IM --<IM, varchar(40),>
			,t0.OT --<OT, varchar(40),>
			,t0.RSLT_D --<RSLT_D, int,>
			,NULL --<DispDirect, uniqueidentifier,>
			,t0.DR --<DR, datetime,>
			,t0.ENP --<ENP, varchar(16),>
			,t1.MCOD --<CODE_MO, varchar(6),>
			,NULL --<ISHOD, int,>
			,NULL --<NSCHET, varchar(15),>
			,NULL --<YEAR, int,>
			,NULL --<MONTH, int,>
			,t0.V016Dispt --<V016Dispt, uniqueidentifier,>
			,@FileID --<DispDirectFactFile, uniqueidentifier,>
			,NULL --<Comments, varchar(4000),>
			,t0.PR_D_N --<PR_D_N, int,>
			,t0.DS1 --<DS1, varchar(10),>
			,t0.Insured --<Insured, uniqueidentifier,>
			,getdate() --<CreationDate, datetime,>
			,NULL --<SchetSluch, uniqueidentifier,>
			,t0.VPOLIS --<VPOLIS, int,>
			,t0.SPOLIS --<SPOLIS, varchar(255),>
			,t0.NPOLIS --<NPOLIS, varchar(255),>
			,t0.SNILS --<SNILS, varchar(255),>
			,t0.P_OTK --<P_OTK, bit,>
			,t0.NHISTORY --<NHISTORY, varchar(255),>
			,t0.DS1_PR --<DS1_PR, bit,>
			,t0.DS_ONK --<DS_ONK, bit,>)
		FROM #ZL t0
		JOIN #ZGLV t1 ON 1 = 1
		WHERE t0.DispDirectFactIdOld IS NULL

		PRINT 'insert old and new [T_DISP_DIRECT_FACT_USLs]' + ' ' + convert(VARCHAR(8), getdate(), 114)

		INSERT INTO [ISZL].[T_DISP_DIRECT_FACT_USL] (
			[DispDirectFactUslID]
			,[DATE_IN]
			,[DATE_OUT]
			,[P_OTK]
			,[CODE_USL]
			,[USL]
			,[PRVS]
			,[CODE_MD]
			,[DispDirectFact]
			)
		SELECT newid() --(<DispDirectFactUslID, uniqueidentifier,>
			,t0.DATE_IN --<DATE_IN, datetime,>
			,t0.DATE_OUT --<DATE_OUT, datetime,>
			,t0.P_OTK --<P_OTK, bit,>
			,t0.CODE_USL --<CODE_USL, varchar(20),>
			,t0.USL --<USL, varchar(254),>
			,t0.PRVS --<PRVS, int,>
			,t0.CODE_MD --<CODE_MD, varchar(25),>
			,t1.DispDirectFactId --<DispDirectFact, uniqueidentifier,>)
		FROM #USL t0
		JOIN #ZL t1 ON t0.ID_PAC = t1.ID_PAC

		PRINT 'link ddf with disp_direct' + ' ' + convert(VARCHAR(8), getdate(), 114)


		if exists (
			select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#LINK')
		)
		DROP TABLE #LINK;
			
		select t0.DispDirectID, t1.DispDirectFactId
		into #LINK
		from iszl.T_DISP_DIRECT t0
		cross apply (
			select top 1 t100.DispDirectFactId
			from ISZL.T_DISP_DIRECT_FACT t100
			join #ZL t101 on t100.DispDirectFactID = t100.DispDirectFactID
			--по ид файла нельзя т.к. часть записей ушло в старый файл а часть в новый
			where t100.[Insured] = t0.Insured
			AND t100.V016Dispt = t0.V016Dispt
			AND YEAR(t100.DateBegin) = t0.[YEAR]
			and t100.DispDirect is null
		) t1
		where not exists (select 1
		from ISZL.T_DISP_DIRECT_FACT t100
		where t100.DispDirect = t0.DispDirectId)

		UPDATE t0
		SET [DispDirect] = t1.DispDirectID
		FROM ISZL.T_DISP_DIRECT_FACT t0
		JOIN #LINK t1 on t0.DispDirectFactID = t1.DispDirectFactId

		if (select count(*) from iszl.T_DISP_DIRECT_FACT where DispDirectFactFile = @FileID)=0
		begin
			exec sp_DispDirectFactFileDel @fileid
		end		
	END

	select * from #SaveFilesScheme order by id

	DROP TABLE #ZL

	DROP TABLE #ZGLV
END