SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_DispanserisationInitToISZL]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_DispanserisationInitToISZL]
GO

---- =============================================
---- Author:      Vitacore (с)
---- Create date: 2019-10-03
---- Description: Формирование пакета диспансерного наблюдения для идентификации в РДС
---- =============================================

CREATE PROCEDURE [dbo].[sp_DispanserisationInitToISZL]
@GuidPackage uniqueidentifier, 
@QueueName varchar(100) = null
AS

BEGIN
set nocount on;

declare @filename varchar(50)
set @filename = (select dp1.FILENAME from AKUZ.T_RDS_DISP_PACKAGE dp1  where dp1.RdsdispPackageID= @GuidPackage)
declare @xml varchar(max)

select distinct
	 dpt.ID_PAC
	,dpt.FAM
	,dpt.IM
	,dpt.OT
	,convert(varchar(10),dpt.DR,120) AS 'DR'
	,case when dpt.VPOLIS = 3 then dpt.NPOLIS else dpt.ENP end as 'ENP'
	--,case when dpt.VPOLIS = '' then null else dpt.VPOLIS end as 'VPOLIS'
	 ,case
	when dpt.VPOLIS = '2' or dpt.VPOLIS = '3' then '3'
	when dpt.VPOLIS = '4' then '2' 
	when dpt.VPOLIS = '1' then '1' 
	else null end as 'VPOLIS' 
	,case when dpt.SPOLIS = '' then null else dpt.SPOLIS end as 'SPOLIS'	  
	,case when dpt.NPOLIS = '' then null else dpt.NPOLIS end as 'NPOLIS' 	  
	,case when dpt.SMO = '' then null else dpt.SMO end as 'SMO' 
	,case when dpt.SNILS = '' then null else dpt.SNILS end as 'SNILS'
into #ZLSDIST
from AKUZ.T_RDS_DISP_PATIENT dpt
where dpt.RdsDispPackage = @GuidPackage

select
	distinct
	convert(varchar(10),dpt.DS1_PR_DATE,120) as 'DATE_BEGIN'
	,convert(varchar(10),dpt.DATE_OUT,120) as 'DATE_OUT'
	,dpt.DS1 as 'DS'
	,dpt.Status as 'STATUS'
	,dpt.CODE_MD as 'SNILS_MR'
	,dpt.V021SpecializationCode as 'SPEC'
	,dpt.ID_PAC as 'ID_PAC'
	,dpt.DISPANSERISATION_INIT_ID
	,dpt.NPR_MO as 'MO'
into #DISPANSERISTATIONINITSDN
from AKUZ.T_RDS_DISP_PATIENT dpt
where dpt.RdsDispPackage = @GuidPackage

;with cte as
(
	select distinct dpt.AmbulanceCard, dpt.ID_PAC from AKUZ.T_RDS_DISP_PATIENT dpt
	where dpt.RdsDispPackage = @GuidPackage
)
select
	 pres.MedicalRecordID as 'PRESENCE_ID' 
	, convert(varchar(10),pres.AppointmentDate,120) as 'DATE_PLAN'
	, case when pres.VisitLocation is null or pres.VisitLocation = 1 then 0
	when pres.VisitLocation = 2 then 1 end as 'PLACE'
	, case when pres.VisitLocation is null or pres.VisitLocation = 1 then lpu_main.LpuCode end as 'MO_P'
	, dispinit.MedicalRecordID as 'DISPINIT_ID'
into #ALLPRESENCES
from cte c
join AKUZ.T_AMBULANCE_CARD ambcard on ambcard.AmbulanceCardID = c.AmbulanceCard
join AKUZ.T_AMBULANCE_CARD_RECORD ambcardrecord on ambcardrecord.AmbulanceCard = ambcard.AmbulanceCardID
join AKUZ.T_DISPANSERISATION_INIT dispinit on dispinit.MedicalRecordID = ambcardrecord.MedicalRecordID
join AKUZ.T_DISPANSERISATION_PRESENCE pres on pres.DispanserisationInit = dispinit.MedicalRecordID
left join AKUZ.T_LPU_MAIN lpu_main on lpu_main.LpuID = pres.LpuMain

set @xml = 
  '<?xml version="1.0" encoding="windows-1251"?>
  ' +
	--ZGLV
	(SELECT 
	   -- Заголовок файла ZGLV
	   (
		SELECT  
		 case when dp.VERSION is null or dp.VERSION = '' then '1.0' else dp.VERSION end AS 'VERSION'
		 ,convert(varchar(10),GETDATE(),120) AS 'DATA'
		 ,dp.FILENAME AS 'FILENAME'
		 ,dp.CODE_MO AS 'MCOD'
		 ,dp.InsuranceCompany as 'SMO'
		FROM AKUZ.T_RDS_DISP_PACKAGE dp
		WHERE dp.RdsdispPackageID =  @GuidPackage
		FOR XML PATH ('ZGLV'),TYPE 
	   )
	   ,( 
		-- Данные ZL
			SELECT
			 zldist.ID_PAC
			,zldist.FAM
			,zldist.IM
			,zldist.OT
			,zldist.DR
			,zldist.ENP
			,zldist.VPOLIS
			,zldist.SPOLIS
			,zldist.NPOLIS			
			,zldist.SNILS
			, (
				SELECT Number as 'CONTACT', 
				case  when PhoneType = 3  then 3 
				when PhoneType in (1,2,5) then 4
				when PhoneType = 4  then 2
				else 3 end as 'TYPE'
				FROM AKUZ.T_PHONE_NUMBER pn where pn.Patient = zldist.ID_PAC
				FOR XML PATH ('CONTACTS'), TYPE
			   )
			, (
				SELECT
					 dn.DISPANSERISATION_INIT_ID as 'DN_ID'
					,dn.DATE_BEGIN
					,dn.DATE_OUT
					,case when t3.Name = 'Снят с выздоровлением' then 4 
					 else
					 case when t3.Name in ('Снят с переводом в группу Д2',
										  'Снят в связи с переводом в другое ЛПУ',
										  'Снят в связи со смертью',
										  'Снят в связи с переводом на другой участок',
										  'Снят в связи с изменением диагноза',
										  'Снят в связи с не подтверждением диагноза',
										  'Снят т.к. не налбюдался более 12 мес.',
										  'Снят/прочее') then 6 end end as 'DN_PR'
					
					,dn.DS
					,dn.MO
					,dn.SNILS_MR
					,dn.SPEC
					,(
						SELECT
							 pres.PRESENCE_ID
							,pres.DATE_PLAN
							,pres.PLACE
							,pres.MO_P
						FROM
						#ALLPRESENCES pres
						where pres.DISPINIT_ID = cast(dn.DISPANSERISATION_INIT_ID as uniqueidentifier)
						FOR XML PATH ('PRESENCES'), TYPE
					 )
				FROM #DISPANSERISTATIONINITSDN dn
				join AKUZ.T_DISPANSERISATION_INIT dinit on dn.DISPANSERISATION_INIT_ID = dinit.MedicalRecordID
				left join AKUZ.T_DISPANSERISATION_END t2 on t2.DispanserisationInit = dinit.MedicalRecordID
				left join VCLib.T_ENUMS t3 on t2.CheckUpStatusEnd = t3.EnumID
				where dn.ID_PAC = zldist.ID_PAC
				FOR XML PATH ('DN'), TYPE
			  )
		FROM #ZLSDIST zldist
		FOR XML PATH('ZL'),TYPE
	   )

	  FOR XML PATH (''), ROOT('DN_LIST')
	  )
DROP TABLE #ZLSDIST
DROP TABLE #DISPANSERISTATIONINITSDN
DROP TABLE #ALLPRESENCES
if (@QueueName is null)
  begin
		SELECT @filename,@xml
  end
  else
  begin
          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(@xml, '')--,<Content, text,>
              ,@GuidPackage--,<MessageID, varchar(255),>
              ,null--,<Headers, varchar(255),>
              ,'utf-8'--@ContentEncoding--,<ContentEncoding, varchar(255),>
              ,'DN_LIST'--@Type--,<Type, varchar(255),>
              ,null--,<UserID, varchar(255),>
              ,0--,<State, int,>)
              ,getdate()--@dateTime--,<CreateDateTime, datetime,>)
              ,null--@OwnerID--,<OwnerID, varchar(255),>
  end
  
END