if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_IRPPackLoad]') and xtype = N'P')
drop procedure [dbo].[sp_IRPPackLoad];
go
create procedure [dbo].[sp_IRPPackLoad]
@FileId uniqueidentifier
 ,@PackageName varchar(max) = null
 ,@FileName varchar(max) = null
 ,@Worker uniqueidentifier = null
 ,@x xml = null
AS 
BEGIN 

SELECT
	NewId() as [IrpID]
 , T.c.value('./ZGLV[1]/VERSION[1]', 'varchar(5)') AS [VERSION]
 , T.c.value('./ZGLV[1]/DATA[1]', 'datetime') AS [DATA]
 , T.c.value('./ZGLV[1]/YEAR[1]', 'int') AS [YEAR]
 , T.c.value('./ZGLV[1]/MONTH[1]', 'int') AS [MONTH]
 , T.c.value('./ZGLV[1]/SMO[1]', 'varchar(5)') AS [SMO]
 , T.c.value('./ZGLV[1]/FILENAME[1]', 'varchar(26)') AS [FILENAME]
 into #IRPF_ZGLV
 FROM   @x.nodes('/IRP_LIST') T(c)

--------------------------------------------IRP
SELECT
	NewID() as [ItemID]
	, NewId() as [PersonInfoId]
	, T.c.value('./N_IRP[1]', 'varchar(36)')					AS [N_IRP]
	, T.c.value('./IRP_TYPE[1]', 'int')							AS [IRP_TYPE]
	, T.c.value('./REG_DATE[1]', 'datetime')					AS [REG_DATE]
	, T.c.value('./REG_NUM[1]', 'varchar(10)')					AS [REG_NUM]
	, T.c.value('./DATE_CREATE[1]', 'datetime')					AS [DATE_CREATE]
	, T.c.value('./DATE_CLOSE[1]', 'datetime')					AS [DATE_CLOSE]
	, T.c.value('./DATE_INTERVAL[1]', 'datetime')				AS [DATE_INTERVAL]
	, T.c.value('./FIO[1]', 'varchar(100)')						AS [FIO]
	, T.c.value('./SPOLIS[1]', 'varchar(10)')					AS [SPOLIS]
	, T.c.value('./NPOLIS[1]', 'varchar(20)')					AS [NPOLIS]
	, T.c.value('./ST_OKATO[1]', 'varchar(5)')					AS [ST_OKATO]
	, T.c.value('./WAY[1]', 'int')								AS [WAY]
	, T.c.value('./insured[1]', 'varchar(36)')					AS [Insured]

	-- HANDLING
	, T.c.value('./HANDLING[1]/TEXT[1]', 'varchar(1000)')		AS [TEXT]
	, T.c.value('./HANDLING[1]/ACTIONS[1]', 'varchar(1000)')	AS [ACTIONS]
	, T.c.value('./HANDLING[1]/MO[1]', 'varchar(36)')			AS [MO] --?
	, T.c.value('./HANDLING[1]/USL_OK[1]', 'int')				AS [USL_OK]
	, T.c.value('./HANDLING[1]/REASON[1]', 'int')				AS [REASON]

	-- COMPLAINT
	, T.c.value('./COMPLAINT[1]/DATE_VIEW[1]', 'datetime')		AS [DATE_VIEW]
	, T.c.value('./COMPLAINT[1]/DATE_RET[1]', 'datetime')		AS [DATE_RET]
	, T.c.value('./COMPLAINT[1]/SUM_RET[1]', 'decimal(15,2)')	AS [SUM_RET]
	, T.c.value('./COMPLAINT[1]/INIT_RET[1]', 'int')			AS [INIT_RET]
	, T.c.value('./COMPLAINT[1]/ORG[1]', 'int')					AS [ORG]
	, T.c.value('./COMPLAINT[1]/JUSTIFY[1]', 'int')				AS [JUSTIFY]
	, T.c.value('./COMPLAINT[1]/STATUS[1]', 'int')				AS [STATUS]
	, T.c.value('./COMPLAINT[1]/RESULT[1]', 'int')				AS [RESULT]

		-- ORG_COMPLAINT
		, T.c.value('./COMPLAINT[1]/ORG_COMPLAINT[1]/MO[1]', 'varchar(6)')	AS [ORG_COMP_MO]

			--COMPLAINT_INFO
			, T.c.value('./COMPLAINT[1]/ORG_COMPLAINT[1]/COMPLAINT_INFO[1]/DATE_IN[1]', 'datetime')	AS [DATE_IN]
			, T.c.value('./COMPLAINT[1]/ORG_COMPLAINT[1]/COMPLAINT_INFO[1]/DATE_OUT[1]', 'datetime')	AS [DATE_OUT]
			, T.c.value('./COMPLAINT[1]/ORG_COMPLAINT[1]/COMPLAINT_INFO[1]/INFO[1]', 'varchar(1000)')	AS [INFO]
			, T.c.value('./COMPLAINT[1]/ORG_COMPLAINT[1]/COMPLAINT_INFO[1]/ACTIONS[1]', 'varchar(1000)')	AS [COMP_ACTIONS]
			, T.c.value('./COMPLAINT[1]/ORG_COMPLAINT[1]/COMPLAINT_INFO[1]/USL_OK[1]', 'int')	AS [COMP_USL_OK]
			, T.c.value('./COMPLAINT[1]/ORG_COMPLAINT[1]/COMPLAINT_INFO[1]/REASON[1]', 'int')	AS [COMP_REASON]

	-- PROPOSITION
	, T.c.value('./PROPOSITION[1]/TEXT[1]', 'varchar(1000)')	AS [PROP_TEXT]
	, T.c.value('./PROPOSITION[1]/USL_OK[1]', 'int')			AS [PROP_USL_OK]

into #IRP_ITEM
FROM   @x.nodes('/IRP_LIST/IRP') T(c)

BEGIN

create table #Errors
(
	IM_POL varchar(10),
	BAS_EL varchar(10),
	OSHIB int,
	COMMENT varchar(250)
)

-- ищем по фио
update t1
set t1.[insured] =  (
select top 1 InsuredId from [ISZL].[T_INSURED] as ins 
where ins.[FIO] = t1.FIO)
from #IRP_ITEM t1
where t1.[insured] is null 

-- ищем серии и номеру полиса
update t1
set t1.[insured] =  (
select top 1 Insured from [ISZL].[T_INSURED_POLICY_RECORD] as ipr 
join ISZL.T_POLICY p on p.PolicyID = ipr.Policy
where p.Number = t1.NPOLIS and p.Series = t1.SPOLIS)
from #IRP_ITEM t1
where t1.[insured] is null 

IF EXISTS(select count(N_IRP) from #IRP_ITEM group by N_IRP having  count(N_IRP) > 1)
BEGIN
	insert into #Errors (COMMENT)
	select 'Обнаружены дубли записей по полю N_IRP!'
END 

if(select count(*) from #IRP_ITEM ii where ii.Insured is null) > 0
begin
	insert into #Errors (COMMENT)
	select 'Не удалось найти застрахованного'
end

if (select count(*) from #Errors) = 0
begin

-- id пользователя
DECLARE @UserId uniqueidentifier
SET @UserId = (select top 1 tu.[UserID] from VCCC.T_USERS tu
join ISZL.T_USERS u on u.UserID = tu.[UserID]
join ISZL.T_POST p on u.Post = p.PostID
where Worker = @Worker)

-- если грузим из папки, то пустышка будет
if(@UserId is null)
begin
set @UserId = (select u.UserID from vclib.T_IDENTITY i
join VCLib.T_USERS u on u.[Identity] = i.[IdentityID]
where i.[IdentityName] = 'iszl_service_user')
end

INSERT INTO [VCCC].[T_APPEAL_TO_CALL_CENTER]
           ([AppealToCallCenterID]
           ,[Number]
           ,[NumberWriting]
           ,[DateTimeCreate]
           ,[DateTimeWriting]
           ,[WayOrganizationID]
           ,[How]
           ,[Theme]
           ,[Text]
           ,[Result]
           ,[Zh_d]
           ,[Otv_t]
           ,[Otv_kon]
           ,[DataPlan]
           ,[DataPlan2]
           ,[DateClose]
           ,[Way]
           ,[ADR]
           ,[PHONE]
           ,[EMAIL]
           ,[Comments]
           ,[IN_RD]
           ,[Deleted]
           ,[DeleteReason]
           ,[Redirected]
           ,[Solution]
           ,[Initiator]
           ,[DateReturn]
           ,[ReturningOrganization]
           ,[ReturningMOSMOID]
           ,[SumReturn]
           ,[OrganizationID]
           ,[ResultChoice]
           ,[ComplaintOrganizationID]
           ,[AddressedToBoss]
           ,[Matches]
           ,[AcceptedUser]
           ,[ResponsibleUser]
           ,[AcceptedAppeal]
           ,[PR_OUT]
           ,[DATE_TIME_CROSS]
           ,[IsCovid]
           ,[VaccinationCOVID]
           ,[CallbackPlatform]
           ,[AppealType]
           ,[type_]
           ,[InformedBox]
           ,[StatusModificationDate]
           ,[Status]
           ,[ChangedUser])
     SELECT
           ii.ItemID--<AppealToCallCenterID, uniqueidentifier,>
           ,ii.[N_IRP]--<Number, varchar(36),>
           ,null--<NumberWriting, varchar(36),>
           ,ii.DATE_CREATE--<DateTimeCreate, datetime,>
           ,GETDATE()--<DateTimeWriting, datetime,>
           ,(select top 1 OrganizationID from ISZL.T_ORGANIZATION)--<WayOrganizationID, uniqueidentifier,>
           ,0--<How, int,>
           ,null--<Theme, uniqueidentifier,>
           ,ii.[TEXT]
           ,ii.[RESULT]
           ,null--<Zh_d, int,>
           ,null--<Otv_t, int,>
           ,null--<Otv_kon, varchar(8),>
           ,null--<DataPlan, datetime,>
           ,null--<DataPlan2, datetime,>
           ,ii.DATE_CLOSE--<DateClose, datetime,>
           ,0--<Way, int,>
           ,null--<ADR, varchar(120),>
           ,null--<PHONE, varchar(20),>
           ,null--<EMAIL, varchar(40),>
           ,null--<Comments, varchar(max),>
           ,null--<IN_RD, int,>
           ,0--<Deleted, bit,>
           ,null--<DeleteReason, varchar(255),>
           ,null--<Redirected, int,>
           ,ii.[RESULT]--<Solution, int,>
           ,0--<Initiator, int,>
           ,ii.[DATE_RET]--<DateReturn, datetime,>
           ,ii.[ORG]--<ReturningOrganization, int,>
           ,null--<ReturningMOSMOID, uniqueidentifier,>
           ,ii.[SUM_RET]--<SumReturn, decimal(15,2),>
           ,org.OrganizationID--<OrganizationID, uniqueidentifier,>
           ,null--<ResultChoice, int,>
           ,null--<ComplaintOrganizationID, uniqueidentifier,>
           ,0--<AddressedToBoss, bit,>
           ,0--<Matches, bit,>
           ,@UserId--<AcceptedUser, uniqueidentifier,>
           ,@UserId--<ResponsibleUser, uniqueidentifier,>
           ,null--<AcceptedAppeal, uniqueidentifier,>
           ,null--<PR_OUT, int,>
           ,null--<DATE_TIME_CROSS, datetime,>
           ,0--<IsCovid, bit,>
           ,0--<VaccinationCOVID, bit,>
           ,0--<CallbackPlatform, bit,>
           ,(select AppealTypeID from ISZL.[T_APPEAL_TYPE] apT where apT.IrpType = ii.[IRP_TYPE])
           ,(select EntityID from VCLIB.T_ENTITIES e where e.[EntName] = 'APPEAL_TO_CALL_CENTER' and e.[Schema] = 'ISZL')--<type_, int,>
           ,0--<InformedBox, bit,>
           ,GETDATE()--<StatusModificationDate, datetime,>
           ,null--<Status, int,>
           ,@UserId--<ChangedUser, uniqueidentifier,>
		   from #IRP_ITEM ii
		   	right join ISZL.[T_INSURED] ins on ins.InsuredID = ii.Insured
			right join ISZL.[T_INSURED_POLICY_RECORD] as ipr on ipr.[Insured] = ii.[Insured]
			right join ISZL.[T_POLICY] as pol on ipr.[Policy] = pol.[PolicyID]
			right join iszl.T_ORGANIZATION org on org.OrganizationID = ipr.Smo
			where ii.[Insured] is not null

INSERT INTO ISZL.T_APPEAL_TO_CALL_CENTER
           ([AppealToCallCenterID]
           ,[Number]
           ,[NumberWriting]
           ,[IrpType]
           ,[DateTimeCreate]
           ,[DateTimeWriting]
           ,[How]
           ,[Theme]
           ,[Text]
           ,[Result]
           ,[Zh_d]
           ,[Otv_t]
           ,[Otv_kon]
           ,[DataPlan]
           ,[DataPlan2]
           ,[DateClose]
           ,[InsuredPolicyRecordZ]
           ,[InsuredPolicyRecordIn]
           ,[InsuredZ]
           ,[InsuredIn]
           ,[Way]
           ,[Z_F]
           ,[Z_I]
           ,[Z_O]
           ,[Z_DR]
           ,[Z_ENP]
           ,[Z_SMO]
           ,[Z_DOCTYPE]
           ,[Z_DOCSER]
           ,[Z_DOCNUM]
           ,[ADR]
           ,[PHONE]
           ,[EMAIL]
           ,[IN_F]
           ,[IN_I]
           ,[IN_O]
           ,[IN_DR]
           ,[IN_ENP]
           ,[IN_SMO]
           ,[IN_DOCTYPE]
           ,[IN_DOCSER]
           ,[IN_DOCNUM]
           ,[Comments]
           ,[IN_RD]
           ,[Deleted]
           ,[DeleteReason]
           ,[Redirected]
           ,[Solution]
           ,[Initiator]
           ,[DateReturn]
           ,[ReturningOrganization]
           ,[SumReturn]
           ,[Organization]
           ,[ResultChoice]
           ,[ComplaintOrganization]
           ,[AddressedToBoss]
           ,[Matches]
           ,[WayOrganization]
           ,[AcceptedUser]
           ,[ResponsibleUser]
           ,[AcceptedAppeal]
           ,[ReturningMOSMO]
           ,[IN_MO]
           ,[PR_OUT]
           ,[DATE_TIME_CROSS]
           ,[IsCovid]
           ,[VaccinationCOVID]
           ,[CallbackPlatform]
           ,[AppealType])
     SELECT
           ii.ItemID
           ,ii.[N_IRP]
           ,null
           ,ii.[IRP_TYPE]
           ,GETDATE()
           ,null
           ,ii.[WAY]
           ,null
           ,ii.[TEXT]
           ,ii.[RESULT]
           ,null
           ,null
           ,null
           ,null--<DataPlan, datetime,>
           ,null--<DataPlan2, datetime,>
           ,ii.[DATE_CLOSE]--<DateClose, datetime,>
           ,ipr.[InsuredPolicyRecordID] --<InsuredPolicyRecordZ, uniqueidentifier,>
           ,null--<InsuredPolicyRecordIn, uniqueidentifier,>
           ,ii.[Insured]--<InsuredZ, uniqueidentifier,>
           ,null--<InsuredIn, uniqueidentifier,>
           ,null--<Way, int,> ТУТ ВООБЩЕ МНОГО ВОПРОСОВ С ЭТИМ ПОЛЕМ
           ,ipr.LastName--<Z_F, varchar(40),>
           ,ipr.FirstName--<Z_I, varchar(40),>
           ,ipr.Patronymic--<Z_O, varchar(40),>
           ,ipr.Birthday--<Z_DR, datetime,>
           ,ins.ENP--<Z_ENP, varchar(16),>
           ,org.Code--<Z_SMO, varchar(5),>
           ,pol.OmsType--<DOCTYPEID, varchar(3),>
           ,pol.Series--<DOCSER, varchar(10),>
           ,pol.Number--<DOCNUM, varchar(20),>
           ,null--<ADR, varchar(120),>
           ,null--<PHONE, varchar(20),>
           ,null--<EMAIL, varchar(40),>
           ,null--<IN_F, varchar(40),>
           ,null--<IN_I, varchar(40),>
           ,null--<IN_O, varchar(40),>
           ,null--<IN_DR, datetime,>
           ,null--<IN_ENP, varchar(16),>
           ,null--<IN_SMO, varchar(5),>
           ,null--<IN_DOCTYPE, varchar(2),>
           ,null--<IN_DOCSER, varchar(10),>
           ,null--<IN_DOCNUM, varchar(20),>
           ,null--<Comments, varchar(max),>
           ,null--<IN_RD, int,>
           ,null--<Deleted, bit,>
           ,null--<DeleteReason, varchar(255),>
           ,null--<Redirected, int,>
           ,ii.[RESULT]--<Solution, int,>
           ,0--<Initiator, int,>
           ,ii.[DATE_RET]--<DateReturn, datetime,>
           ,ii.[ORG]--<ReturningOrganization, int,>
           ,ii.[SUM_RET]--<SumReturn, decimal(15,2),>
           ,org.OrganizationID--<Organization, uniqueidentifier,>
           ,null--<ResultChoice, int,>
           ,null--<ComplaintOrganization, uniqueidentifier,>
           ,0--<AddressedToBoss, bit,>
           ,0--<Matches, bit,>
           ,(select top 1 OrganizationID from ISZL.[T_ORGANIZATION])--<WayOrganization, uniqueidentifier,>
           ,@UserId--<AcceptedUser, uniqueidentifier,>
           ,@UserId--<ResponsibleUser, uniqueidentifier,>
           ,null--<AcceptedAppeal, uniqueidentifier,>
           ,null--<ReturningMOSMO, uniqueidentifier,>
           ,null--<IN_MO, uniqueidentifier,>
           ,null--<PR_OUT, int,>
           ,null--<DATE_TIME_CROSS, datetime,>
           ,0--<IsCovid, int,>
           ,0--<VaccinationCOVID, int,>
           ,null--<CallbackPlatform, bit,>
           ,(select AppealTypeID from ISZL.[T_APPEAL_TYPE] apT where apT.IrpType = ii.[IRP_TYPE])
		from #IRP_ITEM as ii
		right join ISZL.[T_INSURED] ins on ins.InsuredID = ii.Insured
		right join ISZL.[T_INSURED_POLICY_RECORD] as ipr on ipr.[Insured] = ii.[Insured]
		right join ISZL.[T_POLICY] as pol on ipr.[Policy] = pol.[PolicyID]
		right join iszl.T_ORGANIZATION org on org.OrganizationID = ipr.Smo
		where ii.[Insured] is not null


INSERT INTO [VCCC].[T_PERSON_INFO]
           ([PersonInfoID]
           ,[FAM]
           ,[IM]
           ,[OT]
           ,[DR]
           ,[ENP]
           ,[SMOID]
           ,[MOID]
           ,[DOCTYPEID]
           ,[DOCSER]
           ,[DOCNUM]
           ,[AppealToCallCenter]
           ,[type_])
     SELECT
           ii.PersonInfoId--(<PersonInfoID, uniqueidentifier,>
           ,ins.FirstName--<FAM, varchar(40),>
           ,ins.FirstName--<IM, varchar(40),>
           ,ins.Patronymic--<OT, varchar(40),>
           ,ins.Birthday--<DR, datetime,>
           ,ins.ENP--<ENP, varchar(16),>
           ,org.Code--<SMOID, varchar(5),>
           ,null--<MOID, varchar(6),>
           ,pol.OmsType--<DOCTYPEID, varchar(3),>
           ,pol.Series--<DOCSER, varchar(10),>
           ,pol.Number--<DOCNUM, varchar(20),>
           ,ii.ItemID--<AppealToCallCenter, uniqueidentifier,>
           ,(select EntityID from VCLIB.T_ENTITIES e where e.[EntName] = 'PERSON_INFO_Z' and e.[Schema] = 'ISZL')--<type_, int,>
		from #IRP_ITEM ii
		right join ISZL.[T_INSURED] ins on ii.Insured = ins.InsuredID
		right join ISZL.[T_INSURED_POLICY_RECORD] as ipr on ipr.[Insured] = ii.[Insured]
		right join ISZL.[T_POLICY] as pol on ipr.[Policy] = pol.[PolicyID]
		right join iszl.T_ORGANIZATION org on org.OrganizationID = ipr.Smo
		where ii.Insured is not null

INSERT INTO [ISZL].[T_PERSON_INFO_Z]
           ([PersonInfoID]
           ,[InsuredPolicyRecord]
           ,[Insured])
     SELECT
           ii.PersonInfoId --(<PersonInfoID, uniqueidentifier,>
           ,ipr.InsuredPolicyRecordID--<InsuredPolicyRecord, uniqueidentifier,>
           ,ii.Insured--<Insured, uniqueidentifier,>)
		from #IRP_ITEM ii
		right join ISZL.[T_INSURED] ins on ii.Insured = ins.InsuredID
		right join ISZL.[T_INSURED_POLICY_RECORD] as ipr on ipr.[Insured] = ii.[Insured]
		where ii.Insured is not null

	end
END

    select (case 
            when (select COUNT(*) from #Errors) = 0 
            then cast(1 as bit)
            else cast(0 as bit)
        end) as IsOk,
        '<?xml version="1.0" encoding="Windows-1251"?>' 
        + 
        (
            SELECT 
                IM_POL as 'IM_POL'
				,OSHIB as 'OSHIB'
				,BAS_EL as 'BAS_EL'
                ,COMMENT as 'COMMENT'
            FROM #Errors 
            FOR XML PATH('ERR'),ROOT('ROOT')
        ) as Content,
        (case 
            when (select COUNT(*) from #Errors) = 0 
            then cast(0 as bit)
            else cast(1 as bit)
        end) as IsSingleXmlFlk,
        'FLK_'+ @FileName+'.xml'  as [Filename],
        'FLK' as [TargetDirectory],
        null as [CodePage]

DROP TABLE #IRPF_ZGLV
DROP TABLE #IRP_ITEM
END