SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


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

-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2017-11-29
-- Description: Список пациентов, направленных на идентификацию формате XML (DIRECT_IDENT, DN_IDENT)
-- =============================================

CREATE PROCEDURE [dbo].[sp_DirectDispancerISZLXML]
	@GuidPackage uniqueidentifier,
    @QueueName varchar(100) = null
AS
BEGIN
 SET NOCOUNT ON;

   ---------------------  Удаляем результаты загрузки предыдущего акта -----------
 update AKUZ.T_RDS_DISP_PATIENT 
         set  AKUZ.T_RDS_DISP_PATIENT.SMO = null,  AKUZ.T_RDS_DISP_PATIENT.RdsDispPackage = @GuidPackage
	from AKUZ.T_RDS_DISP_PATIENT pt
			where pt.RdsDispPatientID in 
			(
			   select pt.RdsDispPatientID from  AKUZ.T_RDS_DISP_PATIENT pt
			   left join AKUZ.T_RDS_DISP_PACKAGE pc on  pt.RdsDispPackage = pc.RdsdispPackageID
			   where pc.RdsDispPackage = @GuidPackage
			)
   delete from AKUZ.T_RDS_DISP_PACKAGE    where RdsDispPackage = @GuidPackage


  declare @filename varchar(50)
  declare @entityName varchar(100)
 (select @filename = dp1.FILENAME, @entityName = ent.EntName 
  from AKUZ.T_RDS_DISP_PACKAGE dp1 
  join AKUZ.T_RDS_DISP_PROGRAM pr on pr.RdsDispProgramID = dp1.RdsDispProgram
  join AKUZ.T_RDS_DISP_PROGRAM_GROUP gr on gr.RdsDispProgramGroupID = pr.RdsDispProgramGroup
  join VCLib.T_ENTITIES ent on ent.EntityID = gr.Entities
  where dp1.RdsdispPackageID= @GuidPackage)

 declare @xml varchar(max)

 if(@entityName = 'DISPANSERISATION_INIT' or @entityName = 'ONK_REGISTRY_CARD_TALON' or @entityName = 'AMBULANCE_CARD')
 begin
--здесь формировать distinct #ZL
select distinct
	 dpt.ID_PAC
	,dpt.FAM
	,dpt.IM
	,dpt.OT
	,dpt.W
	,case when dpt.DOCSER = '' then null else dpt.DOCSER end as 'DOCSER'
	,case when dpt.DOCNUM = '' then null else dpt.DOCNUM end as 'DOCNUM'
	,case when dpt.DOCTYPE = '' then null else dpt.DOCTYPE end as 'DOCTYPE'
	--,dpt.DOCSER
	--,dpt.DOCNUM
	--,dpt.DOCTYPE
	,convert(varchar(10),dpt.DR,120) AS 'DR'
	,case when dpt.NPOLIS = '' then null else dpt.NPOLIS end as 'NPOLIS' 	  
	,case when dpt.ENP = '' then null 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.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

set @xml = 
  '<?xml version="1.0" encoding="windows-1251"?>
  ' +
	--ZGLV
	(SELECT 
	   -- Заголовок файла ZGLV
	   (
		SELECT  
		 dp.VERSION AS 'VERSION'
		 ,convert(varchar(10),GETDATE(),120) AS 'DATA'
		 ,dp.FILENAME AS 'FILENAME'
		 ,dp.CODE_MO AS 'MCOD'
		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.W
			,zldist.DR
			,zldist.ENP
			,zldist.DOCTYPE
			,zldist.DOCSER
			,zldist.DOCNUM
			,zldist.VPOLIS
			,zldist.SPOLIS
			,zldist.NPOLIS
			,zldist.SMO
			,zldist.SNILS
		FROM #ZLSDIST zldist
		FOR XML PATH('ZL'),TYPE
	   )
	  FOR XML PATH (''), ROOT('IDENTIFICATION_DN')
	  )
DROP TABLE #ZLSDIST
end

--TODO: ANOTHER TYPES

--файловая загрузка
if (@QueueName is null)
  begin	
		SELECT @filename,@xml
  end
  else
  begin
  declare @type varchar(255)
  if(@entityName = 'DISPANSERISATION_INIT' or @entityName = 'ONK_REGISTRY_CARD_TALON' or @entityName = 'AMBULANCE_CARD')
  begin
  set @type = 'IDENTIFICATION_DN'
  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(@xml, '') --,<Content, text,>
              ,@GuidPackage--,<MessageID, varchar(255),>
              ,null--,<Headers, varchar(255),>
              ,'utf-8'--@ContentEncoding--,<ContentEncoding, varchar(255),>
              ,@type--@Type--,<Type, varchar(255),>
              ,null--,<UserID, varchar(255),>
              ,0--,<State, int,>)
              ,getdate()--@dateTime--,<CreateDateTime, datetime,>)
              ,null--@OwnerID--,<OwnerID, varchar(255),>
  end
  
END