
/****** 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 
  ,t2.PostingDate 
  ,t0.Descr 
  ,t0.CdaDocument 
  ,t11.type_  as 't11.type_'
  ,t2.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.RecordTime 
  ,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 t12 ON t12.AmbulanceCardID = t2.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.RecordTime

				-- Собираем элемент 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