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

CREATE PROCEDURE [dbo].[sp_LoadZlList] @x xml
 ,@FileID uniqueidentifier = null
 ,@Worker uniqueidentifier = null
 ,@PackageName varchar(100)
 ,@FileName varchar(100)
 --PREREAD секция
 ,@ID varchar(36)
 ,@DATA varchar(10)
AS 
BEGIN 

declare @flkFileName varchar(100) = 'FLK_' + @FileName + '.XmL'

if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#TEMP_ZGLV'))
DROP TABLE #TEMP_ZGLV;
SELECT
NEWID() AS [ZglvID]
, T.c.value('./VERSION[1]', 'varchar(5)') as [VERSION]
, T.c.value('./ID[1]', 'varchar(36)') as [ID]
, T.c.value('./DATA[1]', 'DATE') as [DATA]
INTO #TEMP_ZGLV
FROM @x.nodes('/ZL_LIST/ZGLV') T(c)

if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#TEMP_INS_POL'))
DROP TABLE #TEMP_INS_POL;
SELECT
NEWID() AS [InsPolID]
, T.c.value('./ID_ZAP[1]', 'INT') as [ID_ZAP]
, 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('./DR[1]', 'DATE') as [DR]
, T.c.value('./DD[1]', 'DATE') as [DD]
, T.c.value('./SEX[1]', 'INT') as [SEX]
, T.c.value('./ENP[1]', 'varchar(16)') as [ENP]
, T.c.value('./SNILS[1]', 'varchar(14)') as [SNILS]
, T.c.value('./DATE_BEGIN[1]', 'DATE') as [DATE_BEGIN]
, T.c.value('./DATE_END[1]', 'DATE') as [DATE_END]
, T.c.value('./DATE_STOP[1]', 'DATE') as [DATE_STOP]
, T.c.value('./SMO[1]', 'varchar(5)') as [SMO]
INTO #TEMP_INS_POL
FROM @x.nodes('/ZL_LIST/INS_POL') T(c)

CREATE INDEX [#TEMP_INS_POL_ID_ZAP] ON #TEMP_INS_POL ([ID_ZAP]);

if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#TEMP_POLICY'))
DROP TABLE #TEMP_POLICY;
SELECT
NEWID() AS [PolicyID]
, T.c.value('../ID_ZAP[1]', 'INT') as [ID_ZAP]
, 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]
INTO #TEMP_POLICY
FROM @x.nodes('/ZL_LIST/INS_POL/POLICY') T(c)

CREATE INDEX [#TEMP_POLICY_ID_ZAP] ON #TEMP_POLICY ([ID_ZAP]);

if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#TEMP_I_DOC'))
DROP TABLE #TEMP_I_DOC;
SELECT
NEWID() AS [IDocID]
, T.c.value('../ID_ZAP[1]', 'INT') as [ID_ZAP]
, T.c.value('./DOCTYPE[1]', 'INT') as [DOCTYPE]
, T.c.value('./DOCSER[1]', 'varchar(10)') as [DOCSER]
, T.c.value('./DOCNUM[1]', 'varchar(20)') as [DOCNUM]
INTO #TEMP_I_DOC
FROM @x.nodes('/ZL_LIST/INS_POL/I_DOC') T(c)

CREATE INDEX [#TEMP_I_DOC_ID_ZAP] ON #TEMP_I_DOC ([ID_ZAP]);

if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#TEMP_MO_PR'))
DROP TABLE #TEMP_MO_PR;
SELECT
NEWID() AS [MoPrID]
, T.c.value('../ID_ZAP[1]', 'INT') as [ID_ZAP]
, T.c.value('./DATE[1]', 'DATE') as [ATTACH_DATE]
, T.c.value('./CAUSE[1]', 'INT') as [CAUSE]
, T.c.value('./MO[1]', 'varchar(6)') as [MO]
, T.c.value('./PODR[1]', 'varchar(16)') as [PODR]
, T.c.value('./UCH[1]', 'INT') as [UCH]
, T.c.value('./STATUS[1]', 'INT') as [STATUS]
INTO #TEMP_MO_PR
FROM @x.nodes('/ZL_LIST/INS_POL/MO_PR') T(c)

CREATE INDEX [#TEMP_MO_PR_ID_ZAP] ON #TEMP_MO_PR ([ID_ZAP]);

if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#TEMP_CONTACTS'))
DROP TABLE #TEMP_CONTACTS;
SELECT
NEWID() AS [ContactsID]
, T.c.value('../ID_ZAP[1]', 'INT') as [ID_ZAP]
, T.c.value('./CONTACT[1]', 'varchar(250)') as [CONTACT]
, T.c.value('./TYPE[1]', 'INT') as [TYPE]
INTO #TEMP_CONTACTS
FROM @x.nodes('/ZL_LIST/INS_POL/CONTACTS') T(c)

CREATE INDEX [#TEMP_CONTACTS_ID_ZAP] ON #TEMP_CONTACTS ([ID_ZAP]);

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

	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 exists(select 1 from #TEMP_ZGLV where ID = 'ФЛК БД')
begin
	insert into #Errors
	select
	t0.ID_ZAP
	, 1
	, 'NAME кодировка работает?'
	, 'FIELD HERE'
	, 'NOTE HERE'
	from #TEMP_INS_POL t0

	insert into #Errors
	select 
	null, null,  @PackageName, '', @FileName
end

insert into #errors (ID_ZAP, CODE, NAME, FIELD, NOTE)
select t0.ID_ZAP, 1, 'Код СМО не найден в F002', 'SMO', ''
from #TEMP_INS_POL t0
left join iszl.T_F002_SMO t1 on isnull(t0.SMO,'-1') = t1.SMOCOD
where t1.SMOCOD is null

insert into #errors (ID_ZAP, CODE, NAME, FIELD, NOTE)
select t0.ID_ZAP, 1, 'Тип полиса не найден в F008', 'VPOLIS', ''
from #TEMP_INS_POL t0
join #TEMP_POLICY t1 on t1.ID_ZAP = t0.ID_ZAP
left join iszl.T_F008_OMS_TYPE t5 on isnull(t1.VPOLIS,'-1') = t5.IDDOC and GETDATE()>=t5.DATEBEG and (GETDATE() <= t5.DATEEND or t5.DATEEND is null)
where t5.IDDOC is null

insert into #errors (ID_ZAP, CODE, NAME, FIELD, NOTE)
select t0.ID_ZAP, 1, 'Тип документа не найден в F011', 'DOCTYPE', ''
from #TEMP_INS_POL t0
join #TEMP_I_DOC t1 on t0.ID_ZAP = t1.ID_ZAP
left join iszl.T_F011_DOCUMENT_TYPE t5 on isnull(t1.DOCTYPE,'-1') = t5.IDDOC and GETDATE()>=t5.DATEBEG and (GETDATE() <= t5.DATEEND or t5.DATEEND is null)
where t5.IDDOC is null

insert into #errors (ID_ZAP, CODE, NAME, FIELD, NOTE)
select t0.ID_ZAP, 1, 'Дубль ID_ZAP', 'ID_ZAP', ''
from #TEMP_INS_POL t0
group by t0.ID_ZAP
having count(*)>1

if (Select COUNT(*) from #Errors) = 0
BEGIN
	INSERT INTO [ISZL].[T_INSURED_POLICY_RECORD_DUMMY]
			   ([InsuredPolicyRecordDummyID]
			   ,[FAM]
			   ,[IM]
			   ,[OT]
			   ,[DR]
			   ,[DD]
			   ,[SEX]
			   ,[ENP]
			   ,[SNILS]
			   ,[DATE_BEGIN]
			   ,[DATE_END]
			   ,[DATE_STOP]
			   ,[SMO]
			   ,[VPOLIS]
			   ,[SPOLIS]
			   ,[NPOLIS]
			   ,[DOCTYPE]
			   ,[DOCSER]
			   ,[DOCNUM]
			   ,[ATTACH_DATE]
			   ,[CAUSE]
			   ,[MO]
			   ,[PODR]
			   ,[UCH]
			   ,[STATUS]
			   )
	SELECT
	t0.[InsPolID]
	, t0.FAM
	, t0.IM
	, t0.OT
	, t0.DR
	, t0.DD
	, t0.SEX
	, t0.ENP
	, t0.SNILS
	, t0.DATE_BEGIN
	, t0.DATE_END
	, t0.DATE_STOP
	, t0.SMO
	, t1.VPOLIS
	, t1.SPOLIS
	, t1.NPOLIS
	, t2.DOCTYPE
	, t2.DOCSER
	, t2.DOCNUM
	, t3.[ATTACH_DATE]
	, t3.CAUSE
	, t3.MO
	, t3.PODR
	, t3.UCH
	, t3.[STATUS]
	FROM #TEMP_INS_POL t0
	JOIN #TEMP_POLICY t1 on t0.ID_ZAP = t1.ID_ZAP
	JOIN #TEMP_I_DOC t2 on t0.ID_ZAP = t2.ID_ZAP
	join #TEMP_MO_PR t3 on t3.ID_ZAP = t0.ID_ZAP
	

    INSERT INTO [ISZL].[T_INSURED_POLICY_RECORD_DUMMY_CONTACT]
           ([InsuredPolicyRecordDummyContactID]
           ,[InsuredPolicyRecordDummy]
           ,[CONTACT]
           ,[CONTACT_TYPE])
	SELECT
	         t0.ContactsID
		   , t1.InsPolID
		   , t0.CONTACT
		   , t0.[TYPE]
	FROM #TEMP_CONTACTS t0
	join #TEMP_INS_POL t1 on t1.ID_ZAP = t0.ID_ZAP
	where t0.CONTACT is not null

	----пример вывода двух файлов 
	--insert into #SaveFilesScheme(Id, IsSaveFilesScheme, SaveJobsCount, [Scheme])
	--select 0, 1 as IsSaveFilesScheme, 2 as SaveJobsCount, 'MultipleXML' as [Scheme]

	--insert into #SaveFilesScheme(Id, Filename, Content, CodePage, TargetDirectory)
	--select 1, 'FLK.xml', '<?xml version="1.0" encoding="utf-8"?><root>Привет1</root>', 'utf-8', 'FLK'

	--insert into #SaveFilesScheme(Id, Filename, Content, CodePage, TargetDirectory)
	--select 2, 'OK.xml', '<?xml version="1.0" encoding="windows-1251"?><root>Привет2</root>', 'windows-1251', 'OUT'

	----пример вывода флк файла
	--insert into #SaveFilesScheme(Id, IsSingleXmlFlk, Filename, Content, CodePage, TargetDirectory)
	--select 3, 1, @flkFileName, '<?xml version="1.0" encoding="windows-1251"?><root>FLK</root>', 'windows-1251', 'FLK'

	-- если вы хотите запустить лексемные процессы
	-- то надо вставить в выходную структуру такую строку
	-- иначе вы потеряете первый из запускаемых лексемных процессов
	-- смотри задачу 3487 в проекте vclib:
	--insert into #SaveFilesScheme(Id, IsSaveFilesScheme, IsSingleXmlFlk)
	--select 99, 0, 0 as IsSaveFilesScheme
	--insert into #SaveFilesScheme(Id, IsSaveFilesScheme, IsSingleXmlFlk)
	--select 99, 0, 0 as IsSaveFilesScheme
	--обращайте внимание на ID вставляемых записей, они имеют огромное значение.
	--Лексемным процессам должна предшествовать (по ID) пустая запись (не ФЛК и не выгрузка файлов)

	insert into #SaveFilesScheme(Id, Lexem)
	select 
	100, 'new Process("ProvideInsuredPolicyRecordProcess", new Apartment().GetInstance("ISZL.INSURED_POLICY_RECORD_DUMMY", Cast("'+ cast([InsPolID] as varchar(36)) +'", "Guid"))).$Run()'
    from #TEMP_INS_POL
	
END
ELSE
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_zap 
		into #errors_zap
		from #Errors 

		declare @flk_xml varchar(max) = 
		(select 
		   '<?xml version="1.0" encoding="utf-8"?>' 
			+ 
			(
				SELECT
					t0.ID
					,'ERROR' as RESULT
					,(
						SELECT 
						ID_ZAP
						,(
							select 
							CODE
							, NAME
							, FIELD
							, NOTE
							FROM #Errors t2
							where t2.ID_ZAP = t1.ID_ZAP
							or (t2.ID_ZAP is null and t1.ID_ZAP is null)
							FOR
							XML PATH('ERRORS'),
							TYPE
						)
						FROM #errors_zap t1
						FOR
						XML PATH('INS_POL'),
						TYPE
					)
				from #TEMP_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/INS_POL/ERRORS/NAME'
END

select * from #SaveFilesScheme order by id

DROP TABLE #TEMP_ZGLV
DROP TABLE #TEMP_INS_POL
DROP TABLE #TEMP_POLICY
DROP TABLE #TEMP_I_DOC
DROP TABLE #TEMP_MO_PR
DROP TABLE #TEMP_CONTACTS
END