IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_MilkDirection]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_MilkDirection]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2022-03-16
-- Description: Загрузка информации по направлениям в молочный регистр
-- =============================================
CREATE PROCEDURE [dbo].[sp_MilkDirection]
    @Xml xml,
    @QueueName varchar(100) = 'RDSSync',
    @dateTime datetime = null,
    @User uniqueidentifier = null
AS
BEGIN

    SET NOCOUNT ON;

    declare @Result varchar(max)
           ,@OperationType varchar(6)
           ,@ContentEncoding varchar(255)
           ,@Type varchar(255)
           ,@OwnerID uniqueidentifier
           ,@RegisterID int
            -- Operation types
           ,@INSERT varchar(6)
           ,@UPDATE varchar(6)
           ,@DELETE varchar(6)

    -- Инициализация
    select @dateTime = ISNULL(@dateTime, GETDATE())
            -- Operation types
          ,@INSERT = 'Insert'
          ,@UPDATE = 'Update'
          ,@DELETE = 'Delete'

    -- Получаем тип операции
    select @ContentEncoding = 'utf-8'
          ,@Type = 'MILK_DIRECTION'
    from @Xml.nodes('MILK_DIRECTION') T(c)

    -- Generate table #MILK_DIRECTION
        select t.c.value('./RegisterID[1]', 'int')											as [RegisterID]
			  ,T.c.value('./RegisterID[1]', 'int')											as [RealIdPatient]
              ,t.c.value('./ID[1]', 'uniqueidentifier')										as [ID]
			  ,t.c.value('./LPU[1]', 'varchar(6)')											as [LPU]
              ,convert(datetime, t.c.value('./EnterDate[1]', 'varchar(10)'), 104)			as [EnterDate]
              ,t.c.value('./LastName[1]', 'varchar(40)')									as [LastName]
              ,t.c.value('./FirstName[1]', 'varchar(40)')									as [FirstName]
              ,t.c.value('./FatherName[1]', 'varchar(40)')									as [FatherName]
              ,t.c.value('./BirthSurname[1]', 'varchar(40)')								as [BirthSurname]
              ,convert(datetime, t.c.value('./Birthday[1]', 'varchar(10)'), 104)			as [Birthday]
			  ,convert(datetime, t.c.value('./Date[1]', 'varchar(10)'), 104)				as [Date]
			  ,t.c.value('./Sex[1]', 'int')													as [Sex]
              ,t.c.value('./Kod[1]', 'varchar(32)')											as [Kod]
              ,t.c.value('./DocSeries[1]', 'varchar(16)')									as [DocSeries]
              ,t.c.value('./DocNumber[1]', 'varchar(16)')									as [DocNumber]
			  ,t.c.value('./DocType[1]', 'int')												as [DocType]
              ,t.c.value('./SNILS[1]', 'varchar(14)')										as [SNILS]
			  ,t.c.value('./ChangeType[1]', 'int')											as [ChangeType]                                          
              ,convert(datetime, t.c.value('./DocIssueDate[1]', 'varchar(10)'), 104)		as [DocIssueDate]
              ,t.c.value('./DocIssueOrg[1]', 'varchar(256)')								as [DocIssueOrg]
              ,t.c.value('./Enp[1]', 'varchar(16)')											as [Enp]
              ,t.c.value('./RealFiasAddress[1]', 'varchar(36)')								as [RealFiasAddress]
              ,t.c.value('./RealFiasHome[1]', 'varchar(36)')								as [RealFiasHome]
              ,t.c.value('./RealCity[1]', 'varchar(100)')									as [RealCity]
              ,t.c.value('./RealCityPrefix[1]', 'varchar(10)')								as [RealCityPrefix]
              ,t.c.value('./RealStreet[1]', 'varchar(100)')									as [RealStreet]
              ,t.c.value('./RealStreetPrefix[1]', 'varchar(10)')							as [RealStreetPrefix]
              ,t.c.value('./RealHouse[1]', 'varchar(100)')									as [RealHouse]
              ,t.c.value('./RealBuild[1]', 'varchar(100)')									as [RealBuild]
              ,t.c.value('./RealStruc[1]', 'varchar(100)')									as [RealStruc]
              ,t.c.value('./RealRoom[1]', 'varchar(100)')									as [RealRoom]
              ,convert(datetime, t.c.value('./RealLiveStartDate[1]', 'varchar(10)'), 104)	as [RealLiveStartDate]
              ,convert(datetime, t.c.value('./RealLiveEndDate[1]', 'varchar(10)'), 104)		as [RealLiveEndDate]
              ,t.c.value('./LegalFiasAddress[1]', 'varchar(36)')							as [LegalFiasAddress]
              ,t.c.value('./LegalFiasHome[1]', 'varchar(36)')								as [LegalFiasHome]
              ,t.c.value('./LegalCity[1]', 'varchar(100)')									as [LegalCity]
              ,t.c.value('./LegalCityPrefix[1]', 'varchar(10)')								as [LegalCityPrefix]
              ,t.c.value('./LegalStreet[1]', 'varchar(100)')								as [LegalStreet]
              ,t.c.value('./LegalStreetPrefix[1]', 'varchar(10)')							as [LegalStreetPrefix]
              ,t.c.value('./LegalHouse[1]', 'varchar(100)')									as [LegalHouse]
              ,t.c.value('./LegalBuild[1]', 'varchar(100)')									as [LegalBuild]
              ,t.c.value('./LegalStruc[1]', 'varchar(100)')									as [LegalStruc]
              ,t.c.value('./LegalRoom[1]', 'varchar(100)')									as [LegalRoom]
              ,convert(datetime, t.c.value('./LegalLiveStartDate[1]', 'varchar(10)'), 104)	as [LegalLiveStartDate]
              ,convert(datetime, t.c.value('./LegalLiveEndDate[1]', 'varchar(10)'), 104)	as [LegalLiveEndDate]
              ,t.c.value('./CitizenShip[1]', 'varchar(3)')									as [CitizenShip]
              ,t.c.value('./ExcludeReason[1]', 'int')										as [ExcludeReason]
            INTO #MILK_DIRECTION
        from @Xml.nodes('MILK_DIRECTION') T(c)

		-- Generate table #MILK_DIRECTION_BENEFIT
		select t.c.value('./DirectionID[1]', 'uniqueidentifier')                  as [DirectionID]
              ,convert(datetime, t.c.value('./DiagDate[1]', 'varchar(10)'), 104)  as [DiagDate]
              ,t.c.value('./Document[1]', 'varchar(16)')                          as [Document]
              ,convert(datetime, t.c.value('./DocumentDate[1]', 'varchar(10)'), 104)  as [DocumentDate]
              ,t.c.value('./Mkb[1]', 'varchar(8)')                                as [Mkb]
              ,t.c.value('./BenefitCode[1]', 'varchar(255)')                      as [BenefitCode]
              ,t.c.value('./BenefitViews[1]', 'int')                              as [BenefitViews]
              ,t.c.value('./PostCode[1]', 'varchar(6)')                           as [PostCode]
              ,t.c.value('./ChangeType[1]', 'int')                                as [ChangeType]

            INTO #MILK_DIRECTION_BENEFIT
        from @Xml.nodes('MILK_DIRECTION/MILK_DIRECTION_BENEFIT') T(c)
        
    -- Поиск по СНИЛС
    update md
    set 
		md.RealIdPatient = mr.ChildCode
    from #MILK_DIRECTION md
        join [RDS].T_MILK_REGISTER mr on mr.SNILS = md.SNILS
    where mr.ExcludeReason is null and
		md.RealIdPatient is null
        and mr.SNILS is not null
        and mr.SNILS <> ''
        and mr.SNILS <> '              '
        and md.ChangeType = 1

    -- Поиск по серии и номеру документа
    update md
    set 
		md.RealIdPatient = mr.ChildCode
    from #MILK_DIRECTION md
        join [RDS].T_MILK_REGISTER mr  on mr.DocSeries = md.DocSeries and mr.DocNumber = md.DocNumber and mr.Birthday = md.Birthday
    where mr.ExcludeReason is null and
		md.RealIdPatient is null
        and mr.DocSeries is not null
        and mr.DocNumber is not null
        and mr.DocSeries <> ''
        and mr.DocNumber <> ''
        and md.ChangeType = 1

    -- Поиск по ФИО + ДР
    update md
    set 
		md.RealIdPatient = mr.ChildCode
    from #MILK_DIRECTION md
        join [RDS].T_MILK_REGISTER mr on mr.LastName = md.LastName and mr.FirstName = md.FirstName and (mr.FatherName = md.FatherName or (mr.FatherName is null and md.FatherName is null)) and mr.Birthday = md.Birthday
    where mr.ExcludeReason is null and
		md.RealIdPatient is null
        and md.ChangeType = 1

    -- Генерируем новый идентификатор
    IF (exists(select * from #MILK_DIRECTION where ChangeType = 1 and RegisterID is null and RealIdPatient is null))
    BEGIN
        exec @RegisterID = [dbo].sp_GenerateIdForEntity 'MILK_REGISTER', 'PrimaryKey', 1

		IF(@RegisterID = 0)
		raiserror ('Не верно сгенерирован идентификатор льготника в регистре!', 16, 10)

	    update rd
	    	set rd.RegisterID = @RegisterID
        from #MILK_DIRECTION rd

    END
	     -- если нашли человека уже в регистре то направление делаем на изменение данных
	else IF (exists(select * from #MILK_DIRECTION where ChangeType = 1 and RegisterID is null and RealIdPatient is not null))
    BEGIN

	    update rd
	    	set rd.RegisterID = RealIdPatient, rd.ChangeType = 2
        from #MILK_DIRECTION rd

    END


    update rp
    set rp.LastName = p.LastName
       ,rp.FirstName = p.FirstName
       ,rp.FatherName = p.FatherName
       ,rp.Birthday = p.Birthday
	   ,rp.Sex = eSex.EnumID
       ,rp.DocSeries = p.DocSeries
       ,rp.DocNumber = p.DocNumber
	   ,rp.DocType = eDoc.EnumID       
	   --,rp.Polis = p.Polis
	   --,rp.OKATO = p.OKATO	   
	   ,rp.City = ISNULL(p.RealCity, p.LegalCity)
	   --,rp.LHouse = ISNULL(p.RealBuild, p.LegalBuild) -- Build - строение, подходит?
	   ,rp.[Block] = ISNULL(p.RealStruc, p.LegalStruc)
	   ,rp.Flat = ISNULL(p.RealRoom, p.LegalRoom)
	   --,rp.LFat = ISNULL(p.RealRoom, p.LegalRoom) -- литера квартиры 	   
	   --,rp.Room = ISNULL(p.RealRoom, p.LegalRoom) -- что значит комната...
	   ,rp.SNILS = p.SNILS
	   ,rp.House = ISNULL(p.RealHouse, p.LegalHouse)
	   ,rp.Street = ISNULL(p.RealStreet, p.LegalStreet)
	   --,rp.ExcludeReason = p.ExcludeReason
	   , rp.LastEditDate = @dateTime
	   , rp.LastEditLpu = p.LPU
    from [RDS].T_MILK_REGISTER rp
        join #MILK_DIRECTION p on p.RegisterID = rp.ChildCode
        join [VCLib].T_ENUMS eSex on eSex.EnumTypeID = 103 and eSex.Code = p.Sex
        join [VCLib].T_ENUMS eDoc on eDoc.EnumTypeID = 105 and eDoc.Code = p.DocType
		left join EHR.T_EHR_RUS_O00015 cs on cs.CODE = p.CitizenShip
		left join EHR.T_EHR_TYPE_MATCHING_DUL dul on dul.DocType = eDoc.EnumID
    where p.ChangeType = 2 and rp.ExcludeReason is null

	update rp
		set rp.ExcludeReason = p.ExcludeReason, rp.LastEditLpu = p.LPU, rp.LastEditDate = @dateTime
    from [RDS].T_MILK_REGISTER rp
        join #MILK_DIRECTION p on p.RegisterID = rp.ChildCode
    where p.ChangeType = 3 and rp.ExcludeReason is null
	and not exists(select * from RDS.T_MILK_REGISTER_DATA mrd where mrd.MilkRegister= rp.MilkRegisterID and mrd.Benefits = '188B840E-35CF-4A09-92CC-A21831646E88') --исключаем 1ю категорию

	update mrd
		set mrd.EndDate = @dateTime, mrd.LastEditDate = @dateTime, mrd.LastEditLpu = p.LPU
    from [RDS].T_MILK_REGISTER_DATA mrd
		join RDS.T_MILK_REGISTER mr on mr.MilkRegisterID = mrd.MilkRegister
        join #MILK_DIRECTION p on p.RegisterID = mr.ChildCode
    where mrd.Benefits <> '188B840E-35CF-4A09-92CC-A21831646E88' and--исключаем 1ю категорию
		p.ChangeType = 3 
		and (mrd.EndDate is null or mrd.EndDate > @dateTime)


INSERT INTO [RDS].[T_MILK_REGISTER]
           (MilkRegisterID
		   ,[ChildCode]
           ,[FirstName]
           ,[LastName]
           ,[FatherName]
           ,[Birthday]
           ,[Sex]
           ,[DocSeries]
           ,[DocNumber]
           ,[DocType]
           --,[Polis]
           --,[OKATO]
           ,[City]
           --,[LHouse]
           ,[Block]
           ,[Flat]
           --,[LFat]
           --,[Room]
           ,[Snils]
           ,[House]
           ,[Street]
           ,[LastEditDate]
           ,[LastEditLpu])
	select	    
		 newid()
		,d.RegisterID
		,[FirstName]
		,[LastName]
		,[FatherName]
		,[Birthday]
		,eSex.EnumID
		,[DocSeries]
		,[DocNumber]
		,eDoc.EnumID
		,ISNULL(d.RealCity, d.LegalCity)
		--,ISNULL(p.RealCity, p.LegalCity)
		,ISNULL(d.RealStruc, d.LegalStruc)
		,ISNULL(d.RealRoom, d.LegalRoom)
		--,ISNULL(p.RealCity, p.LegalCity)
		--,ISNULL(p.RealCity, p.LegalCity)
		,[SNILS]
		,ISNULL(d.RealHouse, d.LegalHouse)
		,ISNULL(d.RealStreet, d.LegalStreet)
		,@dateTime
        ,d.LPU
	from #MILK_DIRECTION d
        join [VCLib].T_ENUMS eSex on eSex.EnumTypeID = 103 and eSex.Code = d.Sex
        join [VCLib].T_ENUMS eDoc on eDoc.EnumTypeID = 105 and eDoc.Code = d.DocType
	where d.ChangeType = 1


INSERT INTO [RDS].[T_MILK_REGISTER_DATA]
	           ([MilkRegisterDataID]
	           ,[MilkRegister]
	           ,[DiagDate]
	           ,[Mkb]
	           ,[Benefits]
			   ,[BeginDate]
               ,[LastEditDate]
			   ,[EndDate]
               ,[LastEditLpu])
	 select		newid()
	           ,r.[MilkRegisterID]
	           ,[DiagDate]
	           ,[Mkb]
	           ,b.[BenefitsID]
			   ,@dateTime
			   ,@dateTime
               ,case when (b.Duration is not null and b.PeriodOfAct is not null and b.CloseType is not null) 
					 then (case when b.CloseType = 0 and b.PeriodOfAct = 0 then DATEADD(DAY,    b.Duration, d.Birthday)
								when b.CloseType = 0 and b.PeriodOfAct = 1 then DATEADD(MONTH,  b.Duration, d.Birthday)
								when b.CloseType = 0 and b.PeriodOfAct = 2 then DATEADD(YEAR,   b.Duration, d.Birthday)
								when b.CloseType = 1 and b.PeriodOfAct = 0 then DATEADD(DAY,    b.Duration, d.[EnterDate])
								when b.CloseType = 1 and b.PeriodOfAct = 1 then DATEADD(MONTH,  b.Duration, d.[EnterDate])
								when b.CloseType = 1 and b.PeriodOfAct = 2 then DATEADD(YEAR,   b.Duration, d.[EnterDate])
								else null end)
                     else null 
				end--<EndDate, datetime,>
                ,d.LPU
	from #MILK_DIRECTION_BENEFIT bd
		join #MILK_DIRECTION d on d.ID = bd.DirectionID
		join [RDS].[T_MILK_REGISTER] r on r.ChildCode = d.RegisterID
		left join [VCLib].T_ENUMS e on e.EnumTypeID = 190 and e.Code = bd.BenefitViews
		join [RDS].T_BENEFITS b on b.BenefitCode = bd.BenefitCode  and b.BenefitViews = e.EnumID
	where bd.ChangeType = 1
		and not exists
        (
            select *
            from [RDS].[T_MILK_REGISTER_DATA] rpb
            where 
				rpb.MilkRegister = r.MilkRegisterID
                and  rpb.Benefits = b.BenefitsID
				--and (b.Private = 0 or (b.Private = 1 and (rpb.LPU_INF = case when Len(d.LPU) > 5 then SUBSTRING(d.LPU, 3, 4) else d.LPU end))) 
				and (rpb.EndDate is null or rpb.EndDate >= GetDate())
        )

    update rpb
    set rpb.EndDate = @dateTime,
		rpb.LastEditDate = @dateTime,
        rpb.LastEditLpu = d.LPU
    from #MILK_DIRECTION_BENEFIT rdb
		join #MILK_DIRECTION d on d.ID = rdb.DirectionID
		join [RDS].[T_MILK_REGISTER] r on r.ChildCode = d.RegisterID
		left join [VCLib].T_ENUMS e on e.EnumTypeID = 190 and e.Code = rdb.BenefitViews
		left join [RDS].T_BENEFITS b on b.BenefitCode = rdb.BenefitCode  and b.BenefitViews = e.EnumID
        join [RDS].T_MILK_REGISTER_DATA rpb on rpb.MilkRegister = r.MilkRegisterID and rpb.Benefits = b.BenefitsID
    where 
		rdb.ChangeType = 3 
		and (rpb.EndDate is null or rpb.EndDate > @dateTime)
	

	-- собираем ответ в АКУЗ, MILK_DIRECTION
    SELECT @Result = 
    ( 
        select 
			 rd.ID           as 'ID'
			,rd.RegisterID   as 'RegisterID'
		from #MILK_DIRECTION rd
		for xml path(''),
		root('MILK_REGISTER')
    ) 

	INSERT INTO [RDS].[T_RABBIT_SEND_OBJECT]
		    ([RabbitSendObjectID] ,[QueueName] ,[Content] ,[MessageID] ,[Headers] ,[ContentEncoding] ,[Type] ,[UserID] ,[State] ,[CreateDateTime] ,[OwnerID])
		select 
		       NewID()--(<RabbitSendObjectID, uniqueidentifier,>
		      ,s.ServerName--,<QueueName, varchar(255),>
		      ,dbo.ConvertTextToBinaryZip(@Result, '')--,<Content, text,>
		      ,null--,<MessageID, varchar(255),>
		      ,null--,<Headers, varchar(255),>
		      ,@ContentEncoding--,<ContentEncoding, varchar(255),>
		      ,@Type--,<Type, varchar(255),>
		      ,@User--,<UserID, varchar(255),>
		      ,0--,<State, int,>)
		      ,@dateTime--,<CreateDateTime, datetime,>)
		      ,@OwnerID--,<OwnerID, varchar(255),>
		from [RDS].T_SYNC_SERVERS s
		where 
			s.ServerName = @QueueName
		    and s.ServerType = 0 -- AKUZ
		    and @Result is not null

	update e
		set id = (select MAX(ChildCode) + 1 from RDS.T_MILK_REGISTER)
	from dbo.T_ENTITY_IDS e
	where e.EntName in ('MILK_REGISTER')

END