SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


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


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

CREATE PROCEDURE [dbo].[sp_DirectDispancerISZLXMLDir]
	@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)

 set @xml = 
  '<?xml version="1.0" encoding="windows-1251"?>
  ' + 
  (SELECT 
   -- Заголовок файла   ZGLV
   (
    SELECT  
     '2.1' AS 'VERSION'
     ,convert(varchar(10),GETDATE(),120) AS 'DATA'    
     ,dp.YearDisp  AS 'YEAR'
     ,dp.MONTH AS 'MONTH'
     ,dp.CODE_MO AS 'CODE_MO'
     ,dp.FILENAME AS 'FILENAME'
    FROM AKUZ.T_RDS_DISP_PACKAGE dp
    WHERE dp.RdsdispPackageID =  @GuidPackage
    FOR XML PATH ('ZGLV'),TYPE 
   )
   ,( 
    -- Данные   PERS
	SELECT 
		dpt.[YEAR] as 'YEAR'
		, DATEPART(MONTH ,dpt.DR) as 'MONTH'
		,dpt.TYPE as 'TYPE'
		--,CASE dpt.[STAGE]
		--	 WHEN  0 THEN 'ДВ1'
		--	 WHEN  1 THEN 'ДВ2'
		--	 WHEN  2 THEN 'ОПВ'
		--	 ELSE ''
		-- END   as 'IDDT'
		 
		 ,dpt.V016DispType as  'IDDT'

		, dpt.PODR as 'PODR'
		, dpt.UCH as 'UCH'
		, dpt.SMO 'SMO' 

		, dpt.FAM   as 'FAM'	
		, dpt.IM   as 'IM'					
		, dpt.OT  as 'OT'	
		, dpt.W   as 'W'
		, convert(varchar(10),dpt.DR,120) AS 'DR'  				
		, dpt.ENP  as 'ENP'	
	 ,(
		SELECT  Number       as 'CONTACT', 
        case  when PhoneType = 3  then 3 -- сотовый 
           when PhoneType in (1,2,5) then 4  -- домашний
           when PhoneType = 4  then 2 -- email
        else 3 end    as 'TYPE'
      FROM  AKUZ.T_PHONE_NUMBER pn where pn.Patient =   dpt.ID_PAC
      FOR XML PATH ('CONTACTS'), TYPE 
		)
     
     , dpt.MR  as 'MR' 
     , dpt.DOCTYPE as 'DOCTYPE' 

     --, REPLACE(dpt.DOCSER, ' ', '' ) as 'DOCSER'
     --, dpt.DOCNUM  as 'DOCNUM'

	 , case  when REPLACE(dpt.DOCSER, ' ', '' ) <> ''  then REPLACE(dpt.DOCSER, ' ', '' )
        else null end    as 'DOCSER'
	 , case  when dpt.DOCNUM <> ''  then dpt.DOCNUM
        else null end    as 'DOCNUM'

     , dpt.SNILS  as 'SNILS'
     --, dpt.VPOLIS  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'

   --  , REPLACE(dpt.SPOLIS, ' ', '' )   as 'SPOLIS'
	, case  when REPLACE(dpt.SPOLIS, ' ', '' )  <> ''  then REPLACE(dpt.SPOLIS, ' ', '' ) 
        else null end    as 'SPOLIS'
    
    -- , dpt.NPOLIS  as 'NPOLIS'
	, case  when dpt.NPOLIS  <> ''  then dpt.NPOLIS
        else null end    as 'NPOLIS'

    FROM AKUZ.T_RDS_DISP_PATIENT dpt
    WHERE dpt.RdsDispPackage = @GuidPackage 
			and dpt.MonthDISP <> 0   --  Если месяц установлен в 0 - диспансеризация не проводится.
    FOR XML PATH('PERS'),TYPE
   )
  FOR XML PATH (''), ROOT('PERS_LIST')
  )


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),>
              ,'DISP_DIRECT'--@Type--,<Type, varchar(255),>
              ,null--,<UserID, varchar(255),>
              ,0--,<State, int,>)
              ,getdate()--@dateTime--,<CreateDateTime, datetime,>)
              ,null--@OwnerID--,<OwnerID, varchar(255),>
  end
  
END

