if OBJECT_ID('sp_ISZL_ReferralXml', 'P') is not null
	drop procedure sp_ISZL_ReferralXml
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Vitacore (с)
-- Create date: 2020-01-31
-- Description:	Выгрузка/загрузка информации направлении на госпитализацию в(из) ISZL
-- =============================================
CREATE PROCEDURE [dbo].[sp_ISZL_ReferralXml]
	@LpuCode varchar(255) = null,
	@Xml xml = null,
	@IsNew bit = 1,
	@ID uniqueidentifier = null,
	@LpuTo varchar(255) = null,
	@FilesXml varchar(max) = null,
	@QueueName varchar(255) = null
AS
BEGIN

	declare @Result varchar(max)
			,@ContentEncoding varchar(255)
			,@Type varchar(255)
			,@OperationType varchar(255)
			,@OwnerID uniqueidentifier

	SET NOCOUNT ON;
	IF (@Xml is null)
	BEGIN
		--выясняем тип операции
		IF(@IsNew = 1)
		BEGIN
			select @OperationType = 'Insert'
		END
		ELSE IF (@IsNew = 0 and not exists (select * from AKUZ.T_HOSPITALIZATION_REFERRAL hr where hr.MedicalRecordID = @ID))
		BEGIN
			select @OperationType = 'Delete'
				select @Result = (select 
                @OperationType            as 'header/TYPE'
                ,@LpuCode                as 'header/MO'
                ,@LpuTo                    as 'header/MO_TO'
				FOR XML PATH(''),
				ROOT('HOSPITALISATION_REFERRAL')
			) 
		END

	select @ContentEncoding = 'utf-8'
   ,@Type = 'HOSPITALISATION_REFERRAL'
   ,@OwnerID = @ID
IF(@OperationType = 'Insert')
BEGIN

	SELECT @Result = ( 
		select 
        @OperationType        as 'header/TYPE'
        ,@LpuCode            as 'header/MO'
        ,@LpuTo                as 'header/MO_TO'

        ,hr.ReferralNumber    as 'body/REFERRAL_NUMBER'
		,convert(varchar(10), ref.ReferralDate, 20) as 'body/REFERRAL_DATE'
		,CASE hr.HospitalizationType
			WHEN 13001 THEN 3
			WHEN 13002 THEN 1
			WHEN 13003 THEN 2
         END as 'body/HOSPITALISATION_TYPE'
        ,br.BranchCode        as 'body/BRANCH_FROM'
        --,''					as 'body/VMP_DATE'
        --,''					as 'body/VMP_NUMBER'
        ,brT.BranchCode        as 'body/BRANCH_TO'
        ,dT.Code            as 'body/DIVISION_TO'
        ,sb.Code                as 'body/V020_STRUCTURE_BED'
        ,ref.MkbCode        as 'body/MKB'
		,convert(varchar(10), hr.PreHospitalDate, 20) as 'body/PLANNED_DATE'
        --,''                as 'body/USL_OK'
        ,p.Code                as 'body/DOC_CODE'
        ,pol.PolicyForm        as 'body/POLICY_TYPE'
        ,pol.Serial            as 'body/POLIS_SERIAL'
        ,pol.Number            as 'body/POLIS_NUMBER'
		,ISNULL(i1.SmoCod, i2.SmoCod) as 'body/SMO'
        ,pat.LastName        as 'body/LAST_NAME'
        ,pat.FirstName        as 'body/FIRST_NAME'
        ,pat.FatherName        as 'body/FATHER_NAME'
        ,(case when pat.Sex = 10301 then 1 else 2 end) as 'body/SEX'
		,convert(varchar(10), pat.Birthday, 20) as 'body/BIRTHDAY'
        ,hr.Phone            as 'body/PHONE'
        ,ISNULL(hr.AnotherRegion, 0) as 'body/ANOTHER_REGION'

		from [AKUZ].T_HOSPITALIZATION_REFERRAL hr 
		join [AKUZ].T_STRUCTURE_BED sb on sb.StructureBedID = hr.StructureBed
		join [AKUZ].T_V002_PROFILE v0 on v0.V002ProfileID = sb.V002Profile
		join [AKUZ].T_REFERRAL ref on ref.MedicalRecordID = hr.MedicalRecordID
		--to
		left join [AKUZ].T_BRANCH brT on brT.LpuID = hr.Branch
		left join [AKUZ].T_DIVISION dT on dT.DivisionID = ref.Division
		--from
		join [AKUZ].T_MEDICAL_RECORD mr on mr.MedicalRecordID = ref.MedicalRecordID
		join [AKUZ].T_POST p on p.ResourceID = mr.Post
		join [AKUZ].T_RESOURCE r on r.ResourceID = p.ResourceID
		join [AKUZ].T_DIVISION d on d.DivisionID = r.Division
		left join [AKUZ].T_BRANCH br on br.LpuID = d.Branch
		--patient
		join [AKUZ].T_AMBULANCE_CARD ac on ac.AmbulanceCardID = mr.AmbulanceCard
		join [AKUZ].T_PATIENT pat on pat.PatientID = ac.Patient
		--polis
		join [AKUZ].T_POLICY pol on pol.PolicyID = [dbo].[fn_GetOmsPolicy](pat.PatientID, ref.ReferralDate)
		left join [AKUZ].T_INSURANCE_COMPANY ic on ic.OrganizationID = pol.InsuranceCompany
		left join [AKUZ].T_INSURER i1 on i1.InsurerID = ic.Insurer
		left join [AKUZ].T_INSURER i2 on i2.InsurerID = pol.Insurer
		where hr.MedicalRecordID = @ID
		FOR XML PATH(''),
		ROOT('HOSPITALISATION_REFERRAL')
		) 
		END

		INSERT INTO [AKUZ].[T_RABBIT_SEND_OBJECT]
		   ([RabbitSendObjectID]
		   ,[QueueName]
		   ,[Content]
		   ,[MessageID]
		   ,[Headers]
		   ,[ContentEncoding]
		   ,[Type]
		   ,[UserID]
		   ,[State]
		   ,[CreateDateTime]
		   ,[OwnerID])
		select
		   NewID()--(<RabbitSendObjectID, uniqueidentifier,>
		   ,@QueueName--,<QueueName, varchar(255),>
		   ,dbo.ConvertTextToBinaryZip(@Result, '')--,<Content, text,>
		   ,null--,<MessageID, varchar(255),>
		   ,null--,<Headers, varchar(255),>
		   ,@ContentEncoding--,<ContentEncoding, varchar(255),>
		   ,@Type--,<Type, varchar(255),>
		   ,null--,<UserID, varchar(255),>
		   ,0--,<State, int,>)
		   ,GETDATE()
		   ,@OwnerID
		END
	ELSE 
	IF (@Xml is not null)
	BEGIN
		select @OperationType = (select T.c.value('./header[1]/TYPE[1]', 'varchar(max)') from @Xml.nodes('HOSPITALISATION_REFERRAL') T(c))
		-- Generate table #HOSPITALIZATION_REFERRAL 
		select
				T.c.value('./header[1]/TYPE[1]', 'varchar(max)') as [OperationType]
				,T.c.value('./header[1]/MO[1]', 'varchar(6)') as [LpuFrom]
				,T.c.value('./header[1]/MO_TO[1]', 'varchar(6)') as [LpuTo]

				,T.c.value('./body[1]/REFERRAL_NUMBER[1]', 'varchar(16)') as [ReferralNumber]
				,T.c.value('./body[1]/REFERRAL_DATE[1]', 'datetime') as [ReferralDate]
				,T.c.value('./body[1]/HOSPITALISATION_TYPE[1]', 'int') as [HospitalizationType]
				,T.c.value('./body[1]/BRANCH_FROM[1]', 'varchar(16)') as [BranchFrom]
				,T.c.value('./body[1]/VMP_DATE[1]', 'datetime') as [VmpDate]
				,T.c.value('./body[1]/VMP_NUMBER[1]', 'int') as [VmpNumber]
				,T.c.value('./body[1]/BRANCH_TO[1]', 'varchar(16)') as [BranchTo] 
				,T.c.value('./body[1]/DIVISION_TO[1]', 'varchar(16)') as [DivivsionTo]
				,T.c.value('./body[1]/V020_STRUCTURE_BED[1]', 'int') as [V020StructureBed]
				,T.c.value('./body[1]/MKB[1]', 'varchar(5)') as [MkbCode]
				,T.c.value('./body[1]/PLANNED_DATE[1]', 'datetime') as [PlannedDate]
				,T.c.value('./body[1]/USL_OK[1]', 'int') as [UslOk]
				,T.c.value('./body[1]/DOC_CODE[1]', 'varchar(100)') as [DocCode]
				,T.c.value('./body[1]/POLICY_TYPE[1]', 'int') as [PolicyType]
				,T.c.value('./body[1]/POLIS_SERIAL[1]', 'varchar(16)') as [PolisSerial]
				,T.c.value('./body[1]/POLIS_NUMBER[1]', 'varchar(16)') as [PolisNumber]
				,T.c.value('./body[1]/SMO[1]', 'varchar(5)') as [Smo]
				,T.c.value('./body[1]/LAST_NAME[1]', 'varchar(32)') as [LastName]
				,T.c.value('./body[1]/FIRST_NAME[1]', 'varchar(32)') as [FirstName]
				,T.c.value('./body[1]/FATHER_NAME[1]', 'varchar(32)') as [FatherName]
				,T.c.value('./body[1]/SEX[1]', 'int') as [Sex]
				,T.c.value('./body[1]/BIRTHDAY[1]', 'datetime') as [Birthday]
				,T.c.value('./body[1]/PHONE[1]', 'varchar(128)') as [Phone]
				,T.c.value('./body[1]/ANOTHER_REGION[1]', 'bit') as [AnotherRegion]
			INTO #HOSPITALIZATION_REFERRAL
		from @Xml.nodes('HOSPITALISATION_REFERRAL') T(c)

		
		if(@OperationType <> 'Delete')
		BEGIN		
		 --INSERT RDS_HOSPITALIZATION_REFERRAL
		INSERT INTO [AKUZ].[T_RDS_HOSPITALIZATION_REFERRAL]
		(	[RdsHospitalizationReferralID]
		   ,[ReferralNumber] 
           ,[ReferralDate] 
           ,[HospitalizationType] 
           ,[Division] 
           ,[BranchFrom]
           ,[PolicyForm]
           ,[PolisSerial]
           ,[PolisNumber]
           ,[Insurer]
           ,[FirstName] 
           ,[LastName] 
           ,[FatherName] 
           ,[Sex] 
           ,[Birthday] 
           ,[Phone] 
           ,[MkbCode]
           ,[StructureBed]
           ,[PlannedDate] 
           ,[DocCode]
     --      ,[PatientID]
		   --,[Diagnosis]
		   --,[ID]
		   --,[HospitalisationDate]
		   ,[AnotherRegion]
		   ,LpuMainFrom)
		SELECT
			newid()
		   ,hr.[ReferralNumber] 
           ,hr.[ReferralDate] 
		   ,(CASE hr.[HospitalizationType]
			WHEN 0 THEN 13001
			WHEN 1 THEN 13002
			WHEN 2 THEN 13003
			ELSE 13001 END) 
           ,dTo.DivisionID 
           ,bF.LpuID
           ,hr.[PolicyType]
           ,hr.[PolisSerial]
           ,hr.[PolisNumber]
           ,i.InsurerID
           ,hr.[FirstName] 
           ,hr.[LastName] 
           ,hr.[FatherName] 
           ,(case when hr.[Sex] = 1 then 10301 else 10302 end)  
           ,hr.[Birthday] 
           ,hr.[Phone] 
           ,hr.[MkbCode]
		   ,(select TOP 1 sb.StructureBedID 
				from [AKUZ].T_STRUCTURE_BED sb
				where sb.Code = hr.[V020StructureBed] 
					and sb.DateBegin <= hr.[ReferralDate]
					and (sb.DateEnd is null or
							sb.DateEnd >= hr.[ReferralDate]))
           ,hr.[PlannedDate] 
           ,hr.[DocCode]
     --      ,hr.[Patient]
		   --,hr.Diagnosis
		   --,hr.ID
		   --,hr.[HospDate]
		   ,case when hr.[AnotherRegion] = '1' then 1
				when hr.[AnotherRegion] = 'true' then 1
				else 0
			end
			,lpFrom.LpuId
		from #HOSPITALIZATION_REFERRAL hr
		left join [AKUZ].T_LPU_MAIN lpTo on lpTo.LpuCode = hr.LpuTo 
		left join [AKUZ].T_BRANCH bTo on bTo.LpuMain = lpTo.LpuID and bTo.BranchCode = hr.BranchTo
		left join [AKUZ].T_DIVISION dTo on dTo.Branch = bTo.LpuID and dTo.Code = hr.DivivsionTo

		left join [AKUZ].T_LPU_MAIN lpFrom on lpFrom.LpuCode = hr.LpuFrom
		left join [AKUZ].T_BRANCH bF on bF.LpuMain = lpFrom.LpuID and bF.BranchCode = hr.BranchFrom

		left join [AKUZ].T_INSURER i on i.SmoCod = hr.Smo
		where 
		not exists (
			select * 
			from [AKUZ].[T_RDS_HOSPITALIZATION_REFERRAL] hr1
			where hr1.[ID] = null --hr.ID
		)
		END
		ELSE
		BEGIN
			delete hr
			from [AKUZ].[T_RDS_HOSPITALIZATION_REFERRAL] hr
			join #HOSPITALIZATION_REFERRAL r on hr.ID = null --r.[ID]
		END

		-- Drop table
		DROP TABLE #HOSPITALIZATION_REFERRAL

		END
END
