IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetSetRDSNosology]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_GetSetRDSNosology]
/****** Object:  StoredProcedure [dbo].[sp_GetSetRDSNosology]    Script Date: 18.11.2015 22:00:23 ******/

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

-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-11-03
-- Description: Загрузка льготников по 12 ВЗН
-- =============================================
create PROCEDURE [dbo].[sp_GetSetRDSNosology]
    @Xml xml = null,
    @ID uniqueidentifier = null,
    @IsNew bit = 1,
    @QueueName varchar(100) = 'RDSSync',
    @dateTime datetime = null,
    @User uniqueidentifier = null
AS
BEGIN

    SET NOCOUNT ON;

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

    select @dateTime = ISNULL(@dateTime, GETDATE())
            -- Operation types
          ,@INSERT = 'Insert'
          ,@UPDATE = 'Update'
          ,@DELETE = 'Delete'
          ,@FTLOAD = 'FTLoad'

    IF (@Xml is not null)
    BEGIN
         -- Получаем тип операции
        select @OperationType = t.c.value('./header[1]/TYPE[1]', 'varchar(36)')
              ,@OwnerID = t.c.value('./header[1]/ID[1]', 'uniqueidentifier')
              ,@Result = cast(@Xml as varchar(max))
        from @Xml.nodes('NOSOLOGY') T(c)

		-- Generate table #NOSOLOGYS
        select t.c.value('./ID[1]', 'uniqueidentifier')								as [ID]
		,t.c.value('./ADRESS[1]', 'varchar(256)')									as [Adress]
		,convert(datetime, t.c.value('./AUTO_DATE_CHANGE[1]', 'varchar(10)'), 104)	as [AutoDateOfChange]
		,t.c.value('./CARD_STATUS[1]', 'varchar(256)')								as [CardStatus]
		,t.c.value('./COMMENT[1]', 'varchar(1024)')									as [Comment]
		,convert(datetime, t.c.value('./CHANGE_DATE[1]', 'varchar(10)'), 104)		as [DateOfChange]
		,convert(datetime, t.c.value('./DECLUSION_DATE[1]', 'varchar(10)'), 104)	as [DateOfDeclusion]
		,convert(datetime, t.c.value('./INCLUSION_DATE[1]', 'varchar(10)'), 104)	as [DateOfInclusion]
		,t.c.value('./EXECUTIVE_AUTHORITY[1]', 'varchar(256)')						as [ExecutiveAuthority]
		,t.c.value('./HEAD[1]', 'varchar(256)')										as [Head]
		,t.c.value('./OMS_NUMBER[1]', 'varchar(30)')								as [OmsNumber]
		,t.c.value('./OMS_SERIAL[1]', 'varchar(30)')								as [OmsSerial]
		,t.c.value('./OPERATION_CODE[1]', 'varchar(128)')							as [OperationCode]
		,t.c.value('./UNIQUE_NUMBER[1]', 'varchar(128)')							as [UniqueNumber]
		,t.c.value('./MKB[1]', 'varchar(10)')										as [Mkb]
		,t.c.value('./DIAGNOSIS[1]', 'varchar(4096)')								as [Diagnosis]
		,t.c.value('./DOC_SERIES[1]', 'varchar(30)')								as [DocSeries]
		,t.c.value('./DOC_NUMBER[1]', 'varchar(30)')								as [DocNumber]
		,convert(datetime, t.c.value('./BIRTHDAY[1]', 'varchar(10)'), 104)			as [Birthday]
		,t.c.value('./FIRST_NAME[1]', 'varchar(60)')								as [FirstName]
		,t.c.value('./LAST_NAME[1]', 'varchar(60)')									as [LastName]
		,t.c.value('./FATHER_NAME[1]', 'varchar(60)')								as [FatherName]
		,convert(datetime, t.c.value('./DOC_ISSUE_DATE[1]', 'varchar(10)'), 104)	as [DocIssueDate]
		,t.c.value('./DOC_ISSUE_ORG[1]', 'varchar(256)')							as [DocIssueOrg]
		,t.c.value('./DECLUSION_LPU[1]', 'varchar(6)')								as [DeclusionLPU]
		,t.c.value('./DOC_TYPE[1]', 'int')											as [DocumentType]
		,t.c.value('./EX178[1]', 'int')												as [ExemptionBy178]
		,t.c.value('./EX890[1]', 'int')												as [ExemptionBy890]
		,t.c.value('./INCLUSION_LPU[1]', 'varchar(6)')								as [InclusionLPU]
		,t.c.value('./REGION[1]', 'varchar(13)')									as [Region]
		,t.c.value('./SEX[1]', 'int')												as [Sex]
		,t.c.value('./RF_ACCOUNT[1]', 'int')										as [SubjectOfRFAccounted]
		,t.c.value('./SNILS[1]', 'varchar(14)')										as [SNILS]
		,t.c.value('./BENIFITS[1]', 'varchar(max)')									as [Benefits]
		,t.c.value('./BIRTHDAY_LAST_NAME[1]', 'varchar(60)')						as [BirthdayLastName]
		,t.c.value('./DIASBILITY[1]', 'int')										as [Disability]
		,t.c.value('./THERAPY_RESISTANCE[1]', 'int')								as [TherapyResistance]
		,convert(datetime, t.c.value('./DEATH_DATE[1]', 'varchar(10)'), 104)		as [DeathDate]
		,t.c.value('./OKATO[1]', 'varchar(max)')									as [OKATO]
		,t.c.value('./SMO[1]', 'varchar(5)')										as [F002Smo]
        INTO #NOSOLOGYS
        from @Xml.nodes('NOSOLOGY/body/NOSOLOGY_ITEMS/NOSOLOGY_ITEM') T(c)
        where @OperationType = @FTLOAD

		-- Generate table #NOSOLOGY
        select t.c.value('./header[1]/ID[1]', 'uniqueidentifier')							as [ID]
		,t.c.value('./body[1]/ADRESS[1]', 'varchar(256)')									as [Adress]
		,convert(datetime, t.c.value('./body[1]/AUTO_DATE_CHANGE[1]', 'varchar(10)'), 104)	as [AutoDateOfChange]
		,t.c.value('./body[1]/CARD_STATUS[1]', 'varchar(256)')								as [CardStatus]
		,t.c.value('./body[1]/COMMENT[1]', 'varchar(1024)')									as [Comment]
		,convert(datetime, t.c.value('./body[1]/CHANGE_DATE[1]', 'varchar(10)'), 104)		as [DateOfChange]
		,convert(datetime, t.c.value('./body[1]/DECLUSION_DATE[1]', 'varchar(10)'), 104)	as [DateOfDeclusion]
		,convert(datetime, t.c.value('./body[1]/INCLUSION_DATE[1]', 'varchar(10)'), 104)	as [DateOfInclusion]
		,t.c.value('./body[1]/EXECUTIVE_AUTHORITY[1]', 'varchar(256)')						as [ExecutiveAuthority]
		,t.c.value('./body[1]/HEAD[1]', 'varchar(256)')										as [Head]
		,t.c.value('./body[1]/OMS_NUMBER[1]', 'varchar(30)')								as [OmsNumber]
		,t.c.value('./body[1]/OMS_SERIAL[1]', 'varchar(30)')								as [OmsSerial]
		,t.c.value('./body[1]/OPERATION_CODE[1]', 'varchar(128)')							as [OperationCode]
		,t.c.value('./body[1]/UNIQUE_NUMBER[1]', 'varchar(128)')							as [UniqueNumber]
		,t.c.value('./body[1]/MKB[1]', 'varchar(10)')										as [Mkb]
		,t.c.value('./body[1]/DIAGNOSIS[1]', 'varchar(4096)')								as [Diagnosis]
		,t.c.value('./body[1]/DOC_SERIES[1]', 'varchar(30)')								as [DocSeries]
		,t.c.value('./body[1]/DOC_NUMBER[1]', 'varchar(30)')								as [DocNumber]
		,convert(datetime, t.c.value('./body[1]/BIRTHDAY[1]', 'varchar(10)'), 104)			as [Birthday]
		,t.c.value('./body[1]/FIRST_NAME[1]', 'varchar(60)')								as [FirstName]
		,t.c.value('./body[1]/LAST_NAME[1]', 'varchar(60)')									as [LastName]
		,t.c.value('./body[1]/FATHER_NAME[1]', 'varchar(60)')								as [FatherName]
		,convert(datetime, t.c.value('./body[1]/DOC_ISSUE_DATE[1]', 'varchar(10)'), 104)	as [DocIssueDate]
		,t.c.value('./body[1]/DOC_ISSUE_ORG[1]', 'varchar(256)')							as [DocIssueOrg]
		,t.c.value('./body[1]/DECLUSION_LPU[1]', 'varchar(6)')								as [DeclusionLPU]
		,t.c.value('./body[1]/DOC_TYPE[1]', 'int')											as [DocumentType]
		,t.c.value('./body[1]/EX178[1]', 'int')												as [ExemptionBy178]
		,t.c.value('./body[1]/EX890[1]', 'int')												as [ExemptionBy890]
		,t.c.value('./body[1]/INCLUSION_LPU[1]', 'varchar(6)')								as [InclusionLPU]
		,t.c.value('./body[1]/REGION[1]', 'varchar(13)')									as [Region]
		,t.c.value('./body[1]/SEX[1]', 'int')												as [Sex]
		,t.c.value('./body[1]/RF_ACCOUNT[1]', 'int')										as [SubjectOfRFAccounted]
		,t.c.value('./body[1]/SNILS[1]', 'varchar(14)')										as [SNILS]
		,t.c.value('./body[1]/BENIFITS[1]', 'varchar(max)')									as [Benefits]
		,t.c.value('./body[1]/BIRTHDAY_LAST_NAME[1]', 'varchar(60)')						as [BirthdayLastName]
		,t.c.value('./body[1]/DIASBILITY[1]', 'int')										as [Disability]
		,t.c.value('./body[1]/THERAPY_RESISTANCE[1]', 'int')								as [TherapyResistance]
		,convert(datetime, t.c.value('./body[1]/DEATH_DATE[1]', 'varchar(10)'), 104)			as [DeathDate]
		,t.c.value('./body[1]/OKATO[1]', 'varchar(max)')									as [OKATO]
		,t.c.value('./body[1]/SMO[1]', 'varchar(5)')										as [F002Smo]
        INTO #NOSOLOGY
        from @Xml.nodes('NOSOLOGY') T(c)
        where @OperationType <> @FTLOAD

		ALTER TABLE #NOSOLOGY ADD [Patient] uniqueidentifier

		ALTER TABLE #NOSOLOGYS ADD [Patient] uniqueidentifier

		-- Связываем регистр с пациентами
         -- Поиск по СНИЛС + ДР
         update nos
         set nos.Patient = p.PatientID
         from #NOSOLOGY nos
             join [AKUZ].T_PATIENT p on p.SNILS = nos.[SNILS] and p.Birthday = nos.[Birthday] 
		where 
			nos.Patient is null
             and nos.[SNILS] is not null
             and nos.[Birthday] is not null

         -- Поиск по серии и номеру документа
         update nos
         set nos.Patient = p.PatientID
         from #NOSOLOGY nos
             join [AKUZ].T_PATIENT p on LEFT(p.DocSeries, 8) = nos.DocSeries and LEFT(p.DocNumber, 8) = nos.DocNumber and p.Birthday = nos.[Birthday]
         where 
			nos.Patient is null
             and nos.DocSeries is not null
             and nos.DocNumber is not null
             and nos.[Birthday] is not null

         -- Поиск по ФИО + ДР
         update nos
         set nos.Patient = p.PatientID
         from #NOSOLOGY nos
             join [AKUZ].T_PATIENT p on p.LastName = nos.LastName and p.FirstName = nos.FirstName and (p.FatherName = nos.FatherName or p.FatherName is null and nos.FatherName is null) and p.Birthday = nos.[Birthday]
         where nos.Patient is null

		-- Поиск по СНИЛС + ДР
         update nos
         set nos.Patient = p.PatientID
         from #NOSOLOGYS nos
             join [AKUZ].T_PATIENT p on p.SNILS = nos.[SNILS] and p.Birthday = nos.[Birthday]
         where 
			nos.Patient is null 
			and nos.[SNILS] is not null
             and nos.[Birthday] is not null 

         -- Поиск по серии и номеру документа
         update nos
         set nos.Patient = p.PatientID
         from #NOSOLOGYS nos
             join [AKUZ].T_PATIENT p on LEFT(p.DocSeries, 8) = nos.DocSeries and LEFT(p.DocNumber, 8) = nos.DocNumber and p.Birthday = nos.[Birthday]
         where 
			nos.Patient is null
             and nos.DocSeries is not null
			and nos.DocNumber is not null
			and	nos.[Birthday] is not null

         -- Поиск по ФИО + ДР
         update nos
         set nos.Patient = p.PatientID
         from #NOSOLOGYS nos
             join [AKUZ].T_PATIENT p on p.LastName = nos.LastName and p.FirstName = nos.FirstName and (p.FatherName = nos.FatherName or p.FatherName is null and nos.FatherName is null) and p.Birthday = nos.[Birthday]
         where nos.Patient is null

		if (@OperationType = @FTLOAD)
        BEGIN

		delete pd 
		from [AKUZ].T_PATIENT_BENEFITS pd 
		join [AKUZ].T_BENEFITS b on b.BenefitsID = pd.Benefits
		where b.BenefitViews = 19006 and CreateType = 2

		INSERT INTO [AKUZ].[T_PATIENT_BENEFITS]
		           ([PatientBenefitsID]
		           ,[Patient]
		           ,[Benefits]
		           ,[BenefitDocument]
		           ,[BenefitDocumentNumber]
		           ,[BeginDate]
		           ,[EndDate]
		           ,[Mkb]
		           ,[Active]
		           ,[Deleted]
		           ,[CreateType]
		           ,[PAC_ID]
		           ,[LPU_INF]
		           ,[SOST_R]
		           ,[ReasonRegistration]
		           ,[ReasonDeregistration]
		           ,[BenefitDocumentSeries]
		           ,[BenefitDocType]
		           ,[RegDate])
		     SELECT
		           newid()
		           ,nos.[Patient]
		           ,(select TOP 1 b.BenefitsID from [AKUZ].T_BENEFITS b 
				   where b.BenefitCode = nos.[Benefits] and b.BenefitViews = 19006)
		           ,null
		           ,null
		           ,nos.DateOfInclusion
		           ,nos.DateOfDeclusion
		           ,(select TOP 1 m.MkbID from [AKUZ].T_MKB m where nos.Mkb = m.MCBCode)
		           ,1
		           ,0
		           ,2
		           ,nos.[UniqueNumber]
		           ,nos.InclusionLPU
		           ,null
		           ,null
		           ,null
		           ,null
		           ,null
		           ,nos.DateOfInclusion
				   FROM #NOSOLOGYS nos
				   where nos.[Patient] is not null
        END
		else if(@OperationType = @DELETE)
        BEGIN

		delete pd 
		from [AKUZ].T_PATIENT_BENEFITS pd 
			join [AKUZ].T_BENEFITS b on b.BenefitsID = pd.Benefits
			join #NOSOLOGY nos on nos.[Patient] = pd.Patient
		where b.BenefitViews = 19006 and pd.CreateType = 2 and b.BenefitCode = nos.[Benefits]

		END
		ELSE
		BEGIN
			delete pd 
			from [AKUZ].T_PATIENT_BENEFITS pd 
				join [AKUZ].T_BENEFITS b on b.BenefitsID = pd.Benefits
				join #NOSOLOGY nos on nos.[Patient] = pd.Patient
			where b.BenefitViews = 19006 and pd.CreateType = 2 and b.BenefitCode = nos.[Benefits]

			INSERT INTO [AKUZ].[T_PATIENT_BENEFITS]
		           ([PatientBenefitsID]
		           ,[Patient]
		           ,[Benefits]
		           ,[BenefitDocument]
		           ,[BenefitDocumentNumber]
		           ,[BeginDate]
		           ,[EndDate]
		           ,[Mkb]
		           ,[Active]
		           ,[Deleted]
		           ,[CreateType]
		           ,[PAC_ID]
		           ,[LPU_INF]
		           ,[SOST_R]
		           ,[ReasonRegistration]
		           ,[ReasonDeregistration]
		           ,[BenefitDocumentSeries]
		           ,[BenefitDocType]
		           ,[RegDate])
		     SELECT
		           newid()
		           ,nos.[Patient]
		           ,(select TOP 1 b.BenefitsID from [AKUZ].T_BENEFITS b 
				   where b.BenefitCode = nos.[Benefits] and b.BenefitViews = 19006)
		           ,null
		           ,null
		           ,nos.DateOfInclusion
		           ,nos.DateOfDeclusion
		           ,(select TOP 1 m.MkbID from [AKUZ].T_MKB m where nos.Mkb = m.MCBCode)
		           ,1
		           ,0
		           ,2
		           ,nos.[UniqueNumber]
		           ,nos.InclusionLPU
		           ,null
		           ,null
		           ,null
		           ,null
		           ,null
		           ,nos.DateOfInclusion
				   FROM #NOSOLOGY nos
				   where nos.[Patient] is not null
		END

		DROP TABLE #NOSOLOGY
		DROP TABLE #NOSOLOGYS
    END
END