
/****** Object:  StoredProcedure [dbo].[pc_getlist_emdr]    Script Date: 30.10.2023 13:31:57 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'sp_sender_get_emdrMessages_V2')
                    AND type IN ( N'P', N'PC' ) ) 
DROP PROCEDURE [dbo].[sp_sender_get_emdrMessages_V2]
GO
--CREATE   PROC [dbo].[sp_sender_get_emdrMessages_V2] @guidlist NVARCHAR(MAX), @send INT, @top INT AS
--BEGIN

--DECLARE @XML XML

--	CREATE TABLE #GuidList (ID UNIQUEIDENTIFIER);
--	if (Len(@guidlist) > 0)
--		begin
--			WHILE len(@guidlist) > 0
--				begin
--					INSERT INTO #GuidList (ID) (SELECT SUBSTRING(@guidlist, 1, 36));
--					set @guidlist = REPLACE(@guidlist, (SELECT SUBSTRING(@guidlist, 1, 36)),'')
--				end
--		end
--	CREATE INDEX IX_TmpGuidList ON #GuidList (ID)




--DECLARE @RESULT TABLE(
--	 EmdrMessageID uniqueidentifier
--	,CardNote uniqueidentifier
--	,MessageID	varchar(255)
--	,MedicalRecordID	uniqueidentifier
--	,EmdrDocType	uniqueidentifier
--	,DocumentNumber	varchar(32)
--	,Descr	varchar(256)
--	,CdaDocument	uniqueidentifier
--	,Division	uniqueidentifier
--	,CardNoteRecipient	uniqueidentifier
--	,PluggableAttributes	varchar(MAX)
--)

--INSERT INTO @RESULT
--SELECT
--  top (@top)
--  t0.EmdrMessageID, 
--  t0.CardNote,
--  t0.MessageID,
--  t1.MedicalRecordID,
--  t1.EmdrDocType,
--  t1.DocumentNumber,
--  t1.Descr, 
--  t1.CdaDocument,
--  t1.Division,
--  t1.CardNoteRecipient,
--  t1.PluggableAttributes 
--FROM AKUZ.T_EMDR_MESSAGE t0 
--	JOIN AKUZ.T_CARD_NOTE t1 ON t1.MedicalRecordID = t0.CardNote
--WHERE 
--    t0.RegistrationState = 0 
--    AND t1.ReadyToBeSent = 1 
--    AND t1.IsDelayed = 0 
--    AND t0.WhereToSend = 1
--	AND NOT EXISTS(SELECT 1 FROM #GuidList T WHERE T.ID = T0.EmdrMessageID)

	
--declare @t2 int = (select  top 1 entityId from vclib.t_entities where entname = 'CARD_NOTE'), 
--@t4 int = (select top 1 entityId from vclib.t_entities where entname = 'CARD_NOTE_FILE'), 
--@t9 int = (select top 1 entityId from vclib.t_entities where entname = 'EHR_RUS_S2DLJAP3QT'),  
--@t23 int = (select  top 1 entityId from vclib.t_entities where entname = 'EHR_RUS_MDR308'),  
--@t25 int = (select  top 1 entityId from vclib.t_entities where entname = 'EHR_RUS_BR9AP1S2QB'),  
--@t27 int = (select  top 1 entityId from vclib.t_entities where entname = 'EHR_RUS_BR9AP1S2QB')


--SELECT
--   NEWID() AS ID
--  ,t0.EmdrMessageID 
--  ,t0.CardNote 
--  ,t2.type_  as 't2.type_'
--  ,t3.FileID 
--  ,t4.type_  as 't4.type_'
--  ,t3.Zipped 
--  ,t0.EmdrDocType 
--  ,t7.Code 	 as 't7.Code'
--  ,t7.IsSemd 
--  ,t7.EhrRusS2DLJAP3QT 
--  ,t9.type_   as 't9.type_'
--  ,t9.CodeEHR  as 't9.CodeEHR'
--  ,t0.DocumentNumber 
--  ,t10.PostingDate 
--  ,t0.Descr 
--  ,t0.CdaDocument 
--  ,t11.type_  as 't11.type_'
--  ,t10.AmbulanceCard 
--  ,t12.Patient 
--  ,t13.LastName 
--  ,t13.FirstName 
--  ,t13.FatherName 
--  ,t13.Birthday 
--  ,t13.Sex 
--  ,t14.Code as 't14.Code' 
--  ,t13.SNILS 
--  ,t15.Snils  as 't15.Snils'
--  ,t13.DocType 
--  ,t16.Code 
--  ,t13.DocSeries 
--  ,t13.DocNumber 
--  ,t12.CardLpu 
--  ,t2.RecordDate 
--  ,t0.Division 
--  ,t19.Branch 
--  ,t20.LpuMain 
--  ,t21.EhrRusMdr308 
--  ,t23.type_  as 't23.type_'
--  ,t23.oid 
--  ,t20.EhrRusBr9ap1s2qb  as 't20.EhrRusBr9ap1s2qb'
--  ,t25.type_ as 't25.type_' 
--  ,t25.CodeEHR 	as 't25.CodeEHR'
--  ,t25.NameEHR as 't25.NameEHR'
--  ,t19.EhrRusBr9ap1s2qb  as 't19.EhrRusBr9ap1s2qb'
--  ,t27.type_  as 't27.type_'
--  ,t27.CodeEHR as 't27.CodeEHR'
--  ,t27.NameEHR as 't27.NameEHR'
--  ,t0.CardNoteRecipient 
--  ,t29.RecipientKind 
--  ,t29.Snils  as 't29.Snils'
--  ,t29.Mdr308Oid 
--  ,t29.Kpp 
--  ,t29.Ogrn 
--  ,t0.PluggableAttributes 
--  ,t0.MessageID	
--  INTO #TMP
--FROM @RESULT t0 
--	JOIN AKUZ.T_MEDICAL_RECORD t2 ON t2.MedicalRecordID = t0.CardNote  
--	JOIN AKUZ.T_AMBULANCE_CARD_RECORD t10 ON t10.MedicalRecordID = t0.CardNote
--	JOIN AKUZ.T_AMBULANCE_CARD t12 ON t12.AmbulanceCardID = t10.AmbulanceCard
--	JOIN AKUZ.T_PATIENT t13 ON t13.PatientID = t12.Patient
--	JOIN AKUZ.T_SNILS t15 ON t15.Snils = t13.SNILS
--	JOIN VCLib.T_ENUMS t16 ON t16.EnumID = t13.DocType
--	JOIN VCLib.T_ENUMS t14 ON t14.EnumID = t13.Sex
--	JOIN AKUZ.T_CARD_NOTE_FILE t3 ON t3.CardNote = t0.MedicalRecordID 
--	JOIN VCLib.T_FILE t4 ON t4.FileID = t3.FileID and t4.type_ = @t4
--	JOIN AKUZ.T_LPU_MAIN t18 ON t18.LpuID = t12.CardLpu
--	JOIN AKUZ.T_DIVISION t19 ON t19.DivisionID = t0.Division 
--	JOIN AKUZ.T_BRANCH t20 ON t20.LpuID = t19.Branch 
--	JOIN AKUZ.T_LPU_MAIN t21 ON t21.LpuID = t20.LpuMain 
--		LEFT JOIN AKUZ.T_EMDR_DOC_TYPE t7 ON t7.EmdrDocTypeID = t0.EmdrDocType 
--		OUTER  APPLY (SELECT @t9 as 'type_', CodeEHR FROM EHR.T_EHR_RUS_S2DLJAP3QT t8 WHERE t8.EhrRusID = t7.EhrRusS2DLJAP3QT) t9 
--		LEFT JOIN AKUZ.T_CDA_DOCUMENT t11 ON t11.CdaDocumentID = t0.CdaDocument 
--		OUTER  APPLY (SELECT @t23 as 'type_', oid FROM EHR.T_EHR_RUS_MDR308 t22 WHERE t22.EhrRusID = t21.EhrRusMdr308) t23
--		OUTER  APPLY (SELECT @t25 as 'type_', CodeEHR, NameEHR FROM EHR.T_EHR_RUS_BR9AP1S2QB t24 WHERE t24.EhrRusID = t20.EhrRusBr9ap1s2qb) t25
--		OUTER  APPLY (SELECT @t27 as 'type_', CodeEHR, NameEHR FROM EHR.T_EHR_RUS_BR9AP1S2QB t26 WHERE t26.EhrRusID = t19.EhrRusBr9ap1s2qb) t27
--		LEFT JOIN AKUZ.T_CARD_NOTE_RECIPIENT t29 ON t29.CardNoteRecipientID = t0.CardNoteRecipient



--SELECT @Xml =(
--SELECT 
--	(SELECT
--		  EMDR_MESSAGE.EmdrMessageID AS '@ID' 
		 
--		 ,(SELECT
--				 CardNote.CardNote		AS '@ID'
--				,CardNote.[t2.type_]	AS '@type'
			
--			-- Собираем элемент CardNoteFile
--			,(SELECT
--					 CardNoteFile.FileID AS '@ID'
--					,CardNoteFile.[t4.type_] AS '@type' 
--					,CardNoteFile.Zipped AS 'Zipped'
					
--					,(SELECT 
--						 CNSI.FileID	AS '@ID'
--						,CNSI_F.type_	AS '@type'
--						,CNSI.SignType
--						,CNSI_F.[Name]
--						,CNSI.ResourceID
--						,CNSI.RoleCode
--						,CNSI.LastName
--						,CNSI.FirstName
--						,CNSI.FatherName
--						,CNSI.Snils
--						,CNSI.EhrRusUk23amlfl7
--						,CNSI.EhrRusLS9R0Q4VR4
--					 FROM AKUZ.T_CARD_NOTE_SIGNATURE CNSI
--					 	JOIN VCLib.T_FILE CNSI_F ON CNSI_F.FileID = CNSI.FileID
--					 WHERE CNSI.CardNoteFile = CardNote.FileID
--					 FOR XML path('AKUZ.CARD_NOTE_SIGNATURE'), type, ELEMENTS) AS 'CardNoteSignatures'

--			  FROM #TMP AS CardNoteFile
--			  WHERE CardNoteFile.ID = CardNote.ID
--			  FOR XML PATH('CardNoteFile'), TYPE, ELEMENTS)

--			-- Собираем элемент EmdrDocType
--			,(SELECT 
--					 EmdrDocType.EmdrDocType AS '@ID'
--					,EmdrDocType.[t7.Code] AS 'Code'
--					,EmdrDocType.IsSemd AS 'IsSemd'
					
--					-- Собираем элемент EhrRusS2DLJAP3QT
--					,(SELECT
--							 EhrRusS2DLJAP3QT.EhrRusS2DLJAP3QT AS '@ID'
--							,EhrRusS2DLJAP3QT.[t9.type_] AS '@type'
--							,EhrRusS2DLJAP3QT.[t9.CodeEHR] AS 'CodeEHR'
--					  FROM #TMP AS EhrRusS2DLJAP3QT
--					  WHERE EhrRusS2DLJAP3QT.ID = CardNote.ID 
--					  FOR XML PATH('EhrRusS2DLJAP3QT'), TYPE, ELEMENTS)

--			  FROM #TMP AS EmdrDocType
--			  WHERE EmdrDocType.ID = CardNote.ID
--			  FOR XML PATH('EmdrDocType'), TYPE, ELEMENTS)

--				,CardNote.DocumentNumber
--				,CardNote.PostingDate
--				,CardNote.Descr
				
--				-- Собираем элемент CdaDocument
--				,(SELECT
--						 CdaDocument.CdaDocument AS '@ID'
--						,CdaDocument.[t11.type_] AS '@type'
--				  FROM #TMP AS CdaDocument
--				  WHERE CdaDocument.ID = CardNote.ID 
--				  FOR XML PATH('CdaDocument'), TYPE, ELEMENTS)

--				-- Собираем элемент AmbulanceCard
--				,(SELECT 
--						AmbulanceCard.AmbulanceCard AS '@ID'
						
--						-- Собираем элемент Patient
--						,(SELECT
--								 Patient.Patient AS '@ID'
--								,Patient.LastName
--								,Patient.FirstName
--								,Patient.FatherName
--								,Patient.Birthday
--								,Patient.Sex			AS 'Sex/@ID'
--								,Patient.[t14.Code]		AS 'Sex/Code'
--								,Patient.SNILS			AS 'SNILS/@ID'
--								,Patient.[t15.Snils]	AS 'SNILS/Snils'
--								,Patient.DocType		AS 'DocType/@ID'
--								,Patient.Code			AS 'DocType/Code'
--								,Patient.DocSeries
--								,Patient.DocNumber

--								,(SELECT 
--									 POL.PolicyID	AS '@ID'
--									,POL.type_		AS '@type'
--									,POL.PolicyForm
--									,POL.Number
--									,POL.IssueDate
--									,POL.ValidThrough
--								FROM AKUZ.T_POLICY POL 
--								WHERE POL.Patient = TMP.Patient
--								FOR XML PATH('AKUZ.POLICY'), TYPE, ELEMENTS) AS 'Policys'

--						  FROM #TMP AS Patient												    
--						  WHERE Patient.ID = CardNote.ID 
--						  FOR XML PATH('Patient'), TYPE, ELEMENTS)
						
--						,AmbulanceCard.CardLpu			AS 'CardLpu/@ID'

--				  FROM #TMP AS AmbulanceCard
--				  WHERE AmbulanceCard.ID = CardNote.ID 
--				  FOR XML PATH('AmbulanceCard'), TYPE, ELEMENTS)

--				,CardNote.RecordDate

--				-- Собираем элемент Division
--				,(SELECT
--						 Division.Division				  AS '@ID'
--						,Division.Branch				  AS 'Branch/@ID'
--						,Division.LpuMain				  AS 'Branch/LpuMain/@ID'
--						,Division.EhrRusMdr308			  AS 'Branch/LpuMain/EhrRusMdr308/@ID'
--						,Division.[t23.type_]			  AS 'Branch/LpuMain/EhrRusMdr308/@type'
--						,Division.oid					  AS 'Branch/LpuMain/EhrRusMdr308/oid'
--						,Division.[t20.EhrRusBr9ap1s2qb]  AS 'Branch/EhrRusBr9ap1s2qb/@ID'
--						,Division.[t25.type_]			  AS 'Branch/EhrRusBr9ap1s2qb/@type'
--						,Division.[t25.CodeEHR]			  AS 'Branch/EhrRusBr9ap1s2qb/CodeEHR'
--						,Division.[t25.NameEHR]			  AS 'Branch/EhrRusBr9ap1s2qb/NameEHR'
--						,Division.[t19.EhrRusBr9ap1s2qb]  AS 'EhrRusBr9ap1s2qb/@ID'
--						,Division.[t27.type_]			  AS 'EhrRusBr9ap1s2qb/@type'
--						,Division.[t27.CodeEHR]			  AS 'EhrRusBr9ap1s2qb/CodeEHR'
--						,Division.[t27.NameEHR]			  AS 'EhrRusBr9ap1s2qb/NameEHR'


--				  FROM #TMP AS Division
--				  WHERE Division.ID = CardNote.ID 
--				  FOR XML PATH('Division'), TYPE, ELEMENTS)
				
--				-- Собираем элемент CardNoteRecipient
--				,(SELECT
--						 CardNoteRecipient.CardNoteRecipient	AS '@ID'
--						,CardNoteRecipient.RecipientKind		AS 'RecipientKind'
--						,CardNoteRecipient.[t29.Snils]			AS 'Snils'
--						,CardNoteRecipient.Mdr308Oid			AS 'Mdr308Oid'
--						,CardNoteRecipient.Kpp					AS 'Kpp'
--						,CardNoteRecipient.Ogrn					AS 'Ogrn'

--				  FROM #TMP AS CardNoteRecipient
--				  WHERE CardNoteRecipient.ID = CardNote.ID 
--				  FOR XML PATH('CardNoteRecipient'), TYPE, ELEMENTS)
				
--				,CardNote.PluggableAttributes

			
--			,(SELECT 
--				 CNS.CardNoteServiceID	AS '@ID'
--				,CNS.ServiceCode		AS 'ServiceCode'
--				,CNS.RenderedDate		AS 'RenderedDate'
--			  FROM AKUZ.T_CARD_NOTE_SERVICE CNS
--			  WHERE CNS.CardNote = CardNote.CardNote
--			  FOR XML PATH('AKUZ.CARD_NOTE_SERVICE'), TYPE, ELEMENTS) AS 'CardNoteServices'

--			,(SELECT 
--			  	 CNA.FileID		AS '@ID'
--			  	,CNA_F.type_	AS '@type'
--			  	,CNA.Target
--			  	,CNA.AssociationType
--			  FROM AKUZ.T_CARD_NOTE_ASSOCIATION CNA
--			  	JOIN VCLib.T_FILE CNA_F ON CNA_F.FileID = CNA.FileID
--			  WHERE CNA.CardNote = CardNote.CardNote
--			  FOR XML path('AKUZ.CARD_NOTE_ASSOCIATION'), type, ELEMENTS) AS 'CardNoteAssociations'
			
			
		 
--		 FROM #TMP CardNote
--		 WHERE CardNote.ID = TMP.ID
--		 FOR XML PATH('CardNote'), TYPE, ELEMENTS 
--			)
--		 ,EMDR_MESSAGE.MessageID	 AS	'MessageID'
--	FROM #TMP EMDR_MESSAGE
--	 WHERE EMDR_MESSAGE.ID = TMP.ID
--	 FOR XML PATH('AKUZ.EMDR_MESSAGE'), TYPE, ELEMENTS
--	 )
	  

--FROM #TMP TMP
--for xml path(''), root('root')
--)

--SELECT @XML

--END;
--GO