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

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2023-11-17
-- Description: Создание СЭМДа рецепта
-- =============================================

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

CREATE PROCEDURE [APS].[sp_CreateSemdProcess] 
    @IdRec uniqueidentifier,
	@IdMis varchar(20), 
	@IdEkzMis varchar(10), 
	@TimeZone varchar(4), 
	@Type varchar(max), 
	@Region varchar(2), 
	@Vers int,
    @Result varchar(max) OUTPUT

AS
BEGIN
SET NOCOUNT ON;

declare @Xml xml, @Xml_Items xml, @XmlDop xml, @XmlTable xml, @recordTarget NVARCHAR(max), @author xml, @custodian xml, @informationRecipient xml,
			@legalAuthenticator xml, @documentationOf xml, @delayedTabl xml, @delayedData xml, @RegionName varchar(max)
set @RegionName = (select top 1 ehr.NameEHR from EHR.T_EHR_RUS_J5PHS5F9RA ehr where ehr.IdEHR = @Region and ehr.NameEHR is not null)

--set @IdMis = 1
--set @IdEkzMis = 1
--set @TimeZone = '0300'
--set @Type = 'Dispense_max'
--set @Region = 16


select  ISNULL((erPharm.oid + '.100.'+ @IdMis + '.' + @IdEkzMis) + '.51','') as 'UnicIdDoc51'
		, ISNULL((erPharm.oid + '.100.'+ @IdMis + '.'  + @IdEkzMis) + '.50','') as 'UnicIdDoc50'
		, ISNULL((erPharm.oid + '.100.'+ @IdMis + '.'  + @IdEkzMis) + '.10','') as 'UnicIdDoc10'
		, ISNULL(r.Serial + Cast(r.Number as varchar(max)) + Cast(DATEPART(YEAR, r.RecipeDate) as varchar(4)) + Cast(p.Code as varchar(100)),'') as 'IdDoc'
		, ISNULL(FORMAT(GETDATE() , 'yyyyMMddHHmmss+') + @TimeZone,'') as 'DocDate'
		, ISNULL(@Vers,1) as 'UnicIdVers'
		, Replace(Replace(ISNULL(r.SNILS,''), '-',''),' ','') as  SNILS
		, ISNULL(r.DocSeries,'') as DocSeries
		, ISNULL(r.DocNumber,'') as DocNumber
		, ISNULL(Cast(ehrDocType.IdEHR as varchar(100)),'') as 'DocType'
		, ISNULL(Cast(ehrDocTypeVers.CurrVersion as varchar(100)),'') as 'DocTypeVers'
		, ISNULL(r.DocIssueOrg,'') as DocIssueOrg
		, ISNULL(r.DocOrgNumber,'') as DocOrgNumber
		, ISNULL(r.PolicyNumber,'') as PolicyNumber
		, ISNULL(r.PolicySerial,'') as PolicySerial
		, ISNULL(FORMAT(r.DocIssueDate , 'yyyyMMddHHmm'),'') as 'DocIssueDate'
		, ISNULL(r.Address,'') as 'Address'
		, ISNULL(@Region,'') as 'Region'
		, ISNULL(@RegionName,'') as 'RegionName'
		, ISNULL(r.FiasAddress,'') as FiasAddress
		, ISNULL(r.FiasHome,'') as FiasHome
		, case when r.Phone is null or Len(r.Phone) = 0 then null
			   when len(Replace(Replace(Replace(Replace(r.Phone, '-', ''), '(', ''), ')', ''),' ', '')) = 0 then null
			   else ('tel:'+ Replace(Replace(Replace(Replace(r.Phone, '-', ''), '(', ''), ')', ''),' ', '')) end as 'Phone'
		, ISNULL(r.LastName,'') as LastName
		, ISNULL(r.FirstName,'') as FirstName
		, (case when r.FatherName  is not null then r.FatherName else null end) as FatherName
		, ISNULL(r.Sex, '') as Sex
		, ISNULL(FORMAT(r.Birthday , 'yyyyMMdd'),'') as 'Birthday'
		, ISNULL(erPharm.oid,'') as 'PharmacyOid'
		, ISNULL(p.LicenseNumber,'') as 'LicenseNumber'
		, ISNULL(p.LicenseIssue,'') as 'LicenseIssue'
		, ISNULL(FORMAT(p.LicenseDate , 'dd.MM.yyyy'),'') as 'LicenseDate'
		, ISNULL(p.OGRN,'') as OGRN
		, ISNULL(p.Name,'') as 'Pharmacy'
		, case when p.Phone is null or Len(p.Phone) = 0 then null
			   when len(Replace(Replace(Replace(Replace(p.Phone, '-', ''), '(', ''), ')', ''),' ', '')) = 0 then null
			   else ('tel:'+ Replace(Replace(Replace(Replace(p.Phone, '-', ''), '(', ''), ')', ''),' ', '')) end as 'PharmacyPhone'
		, ISNULL(('mailto:'+ p.Mail),'') as 'PharmacyMail'
		, ISNULL(p.Address,'') as 'PharmacyAddress'
		, ISNULL(p.PostalCode,'') as 'PostalCode'
		, ISNULL(p.FiasAddress,'') as 'PharmacyFiasAddress'
		, ISNULL(p.FiasHome,'') as 'PharmacyFiasHome'
		, ISNULL(FORMAT(r.IssueDate , 'yyyyMMddHHmm+') + @TimeZone,'') as 'RecipeIssueDate'
		, ISNULL((erPharm.oid + '.100.'+ @IdMis + '.' + @IdEkzMis + '.70'),'') as 'UnicIdPharm'
		, Replace(Replace(ISNULL(w.Snils,''), '-',''),' ','') as 'PostSnils'
		, ISNULL(Cast(postT.IdEHR as varchar(100)),'') as 'PostOid'
		, ISNULL(Cast(postTB.CurrVersion as varchar(100)),'') as 'PostOidVers'
		, ISNULL(postT.NameEHR,'') as 'PostName'
		, ISNULL(w.LastName,'') as 'PostLastName'
		, ISNULL(w.FirstName,'') as 'PostFirstName'
		, (case when w.FatherName  is not null then w.FatherName else null end) as 'PostFatherName'
		, ISNULL(r.Serial,'') as Serial
		, ISNULL(Cast(r.Number as varchar(100)),'') as Number
		, ISNULL((er.oid + '.100.'+ @IdMis + '.' + @IdEkzMis) + '.51','') as 'UnicIdDocMo51'
		, ISNULL((er.oid + '.100.'+ @IdMis + '.' + @IdEkzMis) + '.50','') as 'UnicIdDocMo50'
		, ISNULL(r.Serial + Cast(r.Number as varchar(max)) + Cast(DATEPART(YEAR, r.RecipeDate) as varchar(4)) + er.oid,'') as 'IdDocMO'
into #Recipe
from APS.T_RECIPE r
left join APS.T_LPU_MAIN lp on r.LpuMain = lp.MCOD
left join EHR.T_EHR_RUS_MDR308 er on er.EhrRusID = lp.EhrRusMdr308
left join [VCLib].T_ENUMS eDoc on eDoc.EnumID = r.DocType
left join EHR.T_EHR_TYPE_MATCHING_DUL dul on dul.DocType = eDoc.EnumID
left join EHR.T_EHR_RUS_NN5MEATU5T ehrDocType on ehrDocType.EhrRusID = dul.EhrRusNn5meatu5t
left join EHR.T_EHR_RUS_REF_BOOKS ehrDocTypeVers on ehrDocTypeVers.EhrRusRefBooksID = ehrDocType.EhrRusRefBooks
left join APS.T_RECIPE_INVOICE ri on ri.Recipe = r.RecipeID
left join STOR.T_STORAGE_INVOICE si on si.InvoiceID = ri.InvoiceID
left join STOR.T_STORAGE s on s.StorageID = si.Storage
left join APS.T_PHARMACY_ISSUE_PLACE pip on pip.IssuePlaceID = s.IssuePlace
left join APS.T_PHARMACY p on pip.Pharmacy = p.Code
left join EHR.T_EHR_RUS_MDR308 erPharm on erPharm.EhrRusID = p.EhrRusMdr308
--left join EHR.T_EHR_RUS_REF_BOOKS rbPharm on rbPharm.EhrRusRefBooksID = erPharm.EhrRusRefBooks
left join APS.T_USERS us on us.UserID = si.HoldingUser
left join APS.T_POST post on post.PostID = us.Post
left join APS.T_WORKER w on w.WorkerID = post.Worker
left join EHR.T_EHR_RUS_7PJSAM6GJM postT on postT.EhrRusID = post.EhrRus7pjsam6gjm
left join EHR.T_EHR_RUS_REF_BOOKS postTB on postTB.EhrRusRefBooksID = postT.EhrRusRefBooks
where r.RecipeID = @IdRec

select  ISNULL(ehrEsklp.NameEHR,'') as 'Esklp_Trn'
	   ,ISNULL(ehrEsklp.STANDARD_DOZE,'') as 'Esklp_DoseView'
	   ,ISNULL(ehrEsklp.STANDARD_FORM,'') as 'Esklp_MedicationForms'
	   ,ISNULL(ehrEsklp.NAME_PRODUCER,'') as 'Esklp_MedicamentProducer'
	   ,ISNULL(ehrEsklp.NUMBER_UNITS_2,'') as 'Esklp_PackQuantity'
	   ,ISNULL(Cast(re.Amount as varchar(100)),'') as Amount
	   ,ISNULL(Cast(re.Amount * r.Price as varchar(100)),'') as 'Sum'
	   --,ISNULL((ehrEsklp.NameEHR + ' ' + ehrEsklp.STANDARD_FORM + ' ' + ehrEsklp.STANDARD_DOZE + ' ' + ehrEsklp.NUMBER_UNITS_2 + ' ' + ehrEsklp.NAME_PRODUCER),'') as 'ekslp_name'
	   ,ISNULL(ehrEsklp.NameEHR,'') as 'ekslp_name'
	   ,ISNULL(ehrEsklp.KLP_CODE,'') as 'ekslp_code'
	   ,ISNULL(rbEsklp.CurrVersion,'') as 'ekslp_version'
	   ,ISNULL(ROW_NUMBER() over (order by re.RecipeElementsID),'') as 'Number'

	   ,ISNULL((ehMedDev.NameEHR),'') as 'meddev_name'
	   ,ISNULL((ehMedDev.CHARACTERISTICS),'') as 'meddev_MedicationForms'
	   ,ISNULL(ehMedDev.CodeEHR,'') as 'meddev_code'
	   ,ISNULL(rbMedDev.CurrVersion,'') as 'meddev_version'

	   ,ISNULL((ehNutNut.NameEHR),'') as 'nut_name'
	   ,ISNULL(ehNutNut.CodeEHR,'') as 'nut_code'
	   ,ISNULL((ehNutNut.CHARACTERISTICS),'') as 'nut_MedicationForms'
	   ,ISNULL(rbNut.CurrVersion,'') as 'nut_version'
	   ,ISNULL(FORMAT(GETDATE() , 'yyyyMMddHHmm+') + @TimeZone,'') as 'DocDate'
into #Recipe_Items
from APS.T_RECIPE_ELEMENTS re
left join STOR.T_REMAINS r on r.RemainsID = re.Remains
left join PHARM.T_BILL_OF_GOODS bg  on bg.BillOfGoodsID = r.BillOfGoods
left join PHARM.T_C_TRN trn  on trn.CTrnID = bg.CTrn

left join EHR.T_EHR_RUS_2M69I03GTD ehrEsklp on ehrEsklp.EhrRusID = bg.EhrRus2m69i03gtd
left join EHR.T_EHR_RUS_REF_BOOKS rbEsklp on rbEsklp.EhrRusRefBooksID = ehrEsklp.EhrRusRefBooks

left join EHR.T_EHR_RUS_P08TSH954N ehMedDev on ehMedDev.EhrRusID = trn.EhrRusP08TSH954N
left join EHR.T_EHR_RUS_REF_BOOKS rbMedDev on rbMedDev.EhrRusRefBooksID = ehMedDev.EhrRusRefBooks

left join EHR.T_EHR_RUS_575VSF88OG ehNutNut on ehNutNut.EhrRusID = trn.EhrRus575VSF88OG
left join EHR.T_EHR_RUS_REF_BOOKS rbNut on rbNut.EhrRusRefBooksID = ehNutNut.EhrRusRefBooks
where re.Recipe = @IdRec

--drop table #Recipe
--select * from #Recipe
--select * from #Recipe_Items
select @recordTarget = (
	select 
	'<recordTarget>
		<patientRole>
			<id root="1.2.643.100.3" extension="' + r.snils +'"/>'
			+ (case when r.DocType is not null and r.DocType <> '' and r.DocNumber is not null and  r.DocNumber <> '' and r.DocIssueDate is not null and r.DocIssueDate <> '' 
			then '<identity:IdentityDoc>
					<identity:IdentityCardTypeId code="'+r.DocType +'" codeSystem="1.2.643.5.1.13.13.99.2.48" codeSystemVersion="7.2" codeSystemName="Документы, удостоверяющие личность" displayName="Паспорт гражданина Российской Федерации"/>
					'+ case when r.DocSeries is not null and r.DocSeries <> '' then 
						'<identity:Series>'+r.DocSeries+'</identity:Series>' 
							else '<identity:Series nullFlavor="NI">' end+
					'<identity:Number>'+r.DocNumber+'</identity:Number>'
					+ (case when r.DocIssueOrg is not null and r.DocIssueOrg <> '' then '<identity:IssueOrgName>'+r.DocIssueOrg+'</identity:IssueOrgName>' else '<identity:IssueOrgName nullFlavor="NI"/>' end) 
					+ (case when r.DocOrgNumber is not null and r.DocOrgNumber <> '' then '<identity:IssueOrgCode>'+r.DocOrgNumber+'</identity:IssueOrgCode>' else '<identity:IssueOrgCode nullFlavor="NI"/>' end)
					+ (case when r.DocOrgNumber is not null and r.DocOrgNumber <> '' then '<identity:IssueDate value="'+r.DocIssueDate+'"/>' else '<identity:IssueDate nullFlavor="NI"/>' end)
				+ '</identity:IdentityDoc>' 
			else '<identity:IdentityDoc nullFlavor="NI"/>' end)+
			(case when r.PolicyNumber is not null and r.PolicyNumber <> '' then
			'<identity:InsurancePolicy>
				<identity:InsurancePolicyType '+(case when r.PolicySerial is not null and r.PolicySerial <> '' then 'code="1"' else 'code="2"' end)+' codeSystem="1.2.643.5.1.13.13.11.1035" codeSystemVersion="1.3" codeSystemName="Виды олиса обязательного медицинского страхования" 
				'+(case when r.PolicySerial is not null and r.PolicySerial <> '' then 'displayName="	Полис ОМС старого образца"' else 'displayName="Полис ОМС единого образца, бессрочный"' end)+'/>
				'+(case when r.PolicySerial is not null and r.PolicySerial <> '' then '<identity:Series>'+r.PolicySerial+'</identity:Series>' else '<identity:Series nullFlavor="NI"/>' end)+'
				<identity:Number>'+r.PolicyNumber+'</identity:Number>
			</identity:InsurancePolicy>' else '<identity:InsurancePolicy nullFlavor="NI"/>' end)
			+'<addr use="H">
				<address:Type code="1" codeSystem="1.2.643.5.1.13.13.11.1504" codeSystemVersion="1.3" codeSystemName="Тип адреса пациента" displayName="Адрес по месту жительства (постоянной регистрации)"/>
				<streetAddressLine>'+r.Address+'</streetAddressLine>
				<address:stateCode code="'+r.Region+'" codeSystem="1.2.643.5.1.13.13.99.2.206" codeSystemVersion="6.3" codeSystemName="Субъекты Российской Федерации" displayName="'+r.RegionName+'"/>'+
				'<postalCode nullFlavor="NI"/>'+
					case when r.FiasAddress is not null and r.FiasAddress <> '' and r.FiasHome is not null and r.FiasHome <> '' then 
					'<fias:Address>
						<fias:AOGUID>'+r.FiasAddress+'</fias:AOGUID>
						<fias:HOUSEGUID>'+r.FiasHome+'</fias:HOUSEGUID>			
					</fias:Address>' 
						else '<fias:Address nullFlavor="NI"/>' end+
				'</addr>' +
				(case when r.Phone is null then '' else '<telecom value="'+r.Phone+'"/>' end)
			+'<patient>
				<name>
					<family>'+r.LastName+'</family>
					<given>'+r.FirstName+'</given>'+
					(case when r.FatherName is not null and r.FatherName <> '' then '<identity:Patronymic>'+r.FatherName+'</identity:Patronymic>' else '' end)
					+'</name>
				<administrativeGenderCode '+(case when r.Sex <> 10301 then 'code="2"' else 'code="1"' end)+' codeSystem="1.2.643.5.1.13.13.11.1040" codeSystemVersion="2.1" codeSystemName="Пол пациента"'+(case when r.Sex <> 10301 then ' displayName="Женский"' else ' displayName="Мужской"' end)+'/>
				<birthTime value="'+r.Birthday +'"/>
			</patient>
			<providerOrganization>
				<id root="'+r.PharmacyOid+'"/>
				'+case when r.LicenseNumber = '' or r.LicenseNumber is null then '' else '<id root="1.2.643.5.1.13.2.1.1.1504.101" extension="'+r.LicenseNumber+'" assigningAuthorityName="'+r.LicenseIssue+'. Дата регистрации: '+r.LicenseDate+'."/>' end+'
				<id root="1.2.643.5.1.13.2.1.1.1504.101" extension="ЛО-61-02-003811" assigningAuthorityName="Министерство Здравоохранения Ростовской области. Дата регистрации: 16.09.2019."/>
				<identity:Props>
					<identity:Ogrn>'+r.OGRN+'</identity:Ogrn>
					<identity:Ogrnip nullFlavor="NA"/>
				</identity:Props>
				<name>'+r.Pharmacy+'</name>' +
				(case when r.PharmacyPhone is null then '' else '<telecom value="'+r.PharmacyPhone+'" use="WP"/>' end)
				+'<addr>
					<streetAddressLine>'+r.PharmacyAddress+'</streetAddressLine>
					<address:stateCode code="'+r.Region+'" codeSystem="1.2.643.5.1.13.13.99.2.206" codeSystemVersion="6.3" codeSystemName="Субъекты Российской Федерации" displayName="'+r.RegionName+'"/>'+
					case when r.PostalCode is not null and r.PostalCode <> ''  
					then '<postalCode>'+r.PostalCode+'</postalCode>' 
					else '<postalCode nullFlavor="NI"/>' end+'' +
					case when r.PharmacyFiasAddress is not null and r.PharmacyFiasAddress <> '' and r.PharmacyFiasHome is not null and r.PharmacyFiasHome <> '' then 
					'<fias:Address>
						<fias:AOGUID>'+r.PharmacyFiasAddress+'</fias:AOGUID>
						<fias:HOUSEGUID>'+r.PharmacyFiasHome+'</fias:HOUSEGUID>			
					</fias:Address>' 
						else '<fias:Address nullFlavor="NI"/>' end+
				'</addr>
			</providerOrganization>
		</patientRole>
	</recordTarget>'
	from #Recipe r
	)
	
--select @recordTarget

declare @postalcode varchar(50) = (select (case when r.PostalCode is not null and r.PostalCode <> '' then '<postalCode>'+r.PostalCode+'</postalCode>' 
													else '<postalCode nullFlavor="NI"/>' end)
									from #Recipe r)

select @author = (
	select CAST('' as xml).query('
				declare namespace fias = "urn:hl7-ru:fias"; declare namespace identity = "urn:hl7-ru:identity"; declare namespace address = "urn:hl7-ru:address";
				<author>
				<time nullFlavor="NI"/>
				<assignedAuthor>
					<id root="{sql:column("UnicIdPharm")}" extension="{sql:column("PostSnils")}"/>
					<id root="1.2.643.100.3" extension="{sql:column("PostSnils")}"/>
					<code code="{sql:column("PostOid")}" codeSystem="1.2.643.5.1.13.13.11.1002" codeSystemVersion="{sql:column("PostOidVers")}" codeSystemName="Должности медицинских и фармацевтических работников" displayName="{sql:column("PostName")}"/>
					<assignedPerson>
						<name>
							<family>{sql:column("PostLastName")}</family>
							<given>{sql:column("PostFirstName")}</given>
							<identity:Patronymic>{sql:column("PostFatherName")}</identity:Patronymic>
						</name>
					</assignedPerson>
					<representedOrganization classCode="ORG">
						<id root="{sql:column("PharmacyOid")}"/>
						<name>{sql:column("Pharmacy")}</name>
						<telecom value="{sql:column("PharmacyMail")}"/>
						<addr>
							<streetAddressLine>={sql:column("PharmacyAddress")}</streetAddressLine>
							<address:stateCode code="{sql:column("Region")}" codeSystem="1.2.643.5.1.13.13.99.2.206" codeSystemVersion="6.3" codeSystemName="Субъекты Российской Федерации" displayName="{sql:column("RegionName")}"/>
							*PostalCode*
							<fias:Address nullFlavor ="NI"/>
						</addr>
					</representedOrganization>
				</assignedAuthor>
			</author>')
	from #Recipe r  for xml path('')
	)
	set @author = cast(REPLACE(cast(@author as varchar(max)), '*PostalCode*',  @postalcode) as xml)

--select @author

select @custodian =
	(select CAST('' as xml).query('
				declare namespace fias = "urn:hl7-ru:fias"; declare namespace address = "urn:hl7-ru:address";
				<custodian>
				<assignedCustodian>
				<representedCustodianOrganization classCode="ORG">
					<id root ="{sql:column("PharmacyOid")}"/>
					<name>{sql:column("Pharmacy")}</name>
					<telecom value ="{sql:column("PharmacyPhone")}"/>
					<addr>
						<streetAddressLine>{sql:column("PharmacyAddress")}</streetAddressLine>
						<address:stateCode code="{sql:column("Region")}" codeSystem="1.2.643.5.1.13.13.99.2.206" codeSystemVersion="6.3" codeSystemName="Субъекты Российской Федерации" displayName="{sql:column("RegionName")}"/>
						*PostalCode*
						<fias:Address nullFlavor ="NI"/>
					</addr>
				</representedCustodianOrganization>
			</assignedCustodian> 
		</custodian>' ) 
	from #Recipe r for xml path('')
	)
	set @custodian = cast(REPLACE(cast(@custodian as varchar(max)), '*PostalCode*',  @postalcode) as xml)
--select @custodian

select @informationRecipient =
	Cast('<informationRecipient>
			<intendedRecipient>
				<receivedOrganization>
					<id root="1.2.643.5.1.13"/>
					<name>Министерство здравоохранения Российской Федерации</name>
				</receivedOrganization>
			</intendedRecipient>
		</informationRecipient>' as xml)
--select @informationRecipient

select @legalAuthenticator = (
	SELECT CAST('' as xml).query('
			declare namespace fias = "urn:hl7-ru:fias";declare namespace identity = "urn:hl7-ru:identity"; declare namespace address = "urn:hl7-ru:address";
			<legalAuthenticator>
			<time nullFlavor="NI"/>
			<signatureCode nullFlavor="NI"/>
			<assignedEntity>
				<id root ="{sql:column("UnicIdPharm")}" extension ="{sql:column("PostSnils")}"/>
				<id root ="1.2.643.100.3" extension ="{sql:column("PostSnils")}"/>
				<code code ="{sql:column("PostOid")}" codeSystem="1.2.643.5.1.13.13.11.1002" codeSystemVersion = "{sql:column("PostOidVers")}" codeSystemName="Должности медицинских и фармацевтических работников" displayName ="{sql:column("PostName")}"/>
				<assignedPerson>
					<name>
						<family>{sql:column("PostLastName")}</family>
						<given>{sql:column("PostFirstName")}</given>
						<identity:Patronymic>{sql:column("PostFatherName")}</identity:Patronymic>
					</name>
				</assignedPerson>
				<representedOrganization classCode = "ORG">
					<id root ="{sql:column("PharmacyOid")}"/>
					<name>{sql:column("Pharmacy")}</name>
					<telecom value ="{sql:column("PharmacyPhone")}"/>
					<telecom value ="{sql:column("PharmacyMail")}"/>
					<addr>
						<streetAddressLine>{sql:column("PharmacyAddress")}</streetAddressLine>
						<address:stateCode code="{sql:column("Region")}" codeSystem="1.2.643.5.1.13.13.99.2.206" codeSystemVersion="6.3" codeSystemName="Субъекты Российской Федерации" displayName="{sql:column("RegionName")}"/>
						*PostalCode*
						<fias:Address nullFlavor ="NI"/>
					</addr>
				</representedOrganization>
			</assignedEntity>
		</legalAuthenticator>')
	from #Recipe r  for xml path('')
	)
	set @legalAuthenticator = cast(REPLACE(cast(@legalAuthenticator as varchar(max)), '*PostalCode*',  @postalcode) as xml)
--select @legalAuthenticator


select @documentationOf = (
SELECT CAST('' as xml).query(
		'<documentationOf>
			<serviceEvent>
				<code code="54" codeSystem="1.2.643.5.1.13.13.99.2.726" codeSystemName="Типы документированных событий" displayName="Отпуск лекарственного препарата" codeSystemVersion="3.1"/>
				<effectiveTime value ="{sql:column("RecipeIssueDate")}"/>
			</serviceEvent>
		</documentationOf>')
		from #Recipe r  for xml path('')
		)
--select @documentationOf

declare @TypeDelay varchar(1), @NameDelay varchar(100), @VersDelay varchar(10)

--Доработать после того как Лена выяснить алгоритм в СТП
if(@TypeDelay is not null)
begin
	select @delayedTabl = (
		select '<table>
					<caption>
						Информация об отсроченном обслуживании
					</caption>
					<tbody>
						<tr>
							<td>
								<content>Отсроченное обслуживание</content>
							</td>
							<td>
								<content>'+ISNULL(@NameDelay,'')+'</content>
							</td>
						</tr>
					</tbody>
				</table>'
	)
	
	select @delayedData = (
		select 'declare namespace fias = "urn:hl7-ru:fias";
				<entry>
					<observation classCode="OBS" moodCode="EVN">
						<code code="6013" codeSystem="1.2.643.5.1.13.13.99.2.166" codeSystemVersion="2.1" codeSystemName="Кодируемые поля CDA документов" displayName="Отсроченное обслуживание">
							<originalText>
								<reference value="#DOC_9"/>
							</originalText>
						</code>
						<value xsi:type="CD" code="'+ISNULL(@TypeDelay,'')+'" codeSystem="1.2.643.5.1.13.13.99.2.637" codeSystemVersion="'+ISNULL(@VersDelay,'')+'" codeSystemName="Отсроченное обслуживание" displayName="'+ISNULL(@NameDelay,'')+'"/>
					</observation>
				</entry>'
	)
end

--- Добавил времянку для удаления лишних символов в выгрузке
--SELECT 
--	 Esklp_Trn--LTRIM(RTRIM(REPLACE(Esklp_Trn,' ','_'))) as 'Esklp_Trn'
--	,meddev_name--LTRIM(RTRIM(REPLACE(meddev_name,' ','_'))) as 'meddev_name'
--	,nut_name--LTRIM(RTRIM(REPLACE(nut_name,' ','_'))) as 'nut_name'
--	,Number
--	,Esklp_DoseView
--	,Esklp_MedicationForms
--	,Esklp_MedicamentProducer
--	,Esklp_PackQuantity
--	,nut_MedicationForms
--	,Amount
--	,[Sum]
--INTO #RecipeForXML
--FROM #Recipe_Items
if(@Type = 'Dispense_max')
begin
	set @XmlDop = '
				<section>
					<code code="MEDDISPENSE" codeSystem="1.2.643.5.1.13.13.99.2.197" codeSystemVersion="2.1" codeSystemName="Секции CDA документов" displayName="Отпуск лекарственного препарата/изделия медицинского назначения/специализированного продукта лечебного питания"/>
					<title>Отпуск</title>
					<text>
						<table>
							<thead>
								<tr>
									<th>Торговое наименование</th>
									<th>Нормализованная доза препарата</th>
									<th>Форма отпущенного препарата</th>
									<th>Производитель</th>
									<th>Количество единиц в одной упаковке</th>
									<th>Отпущено (в потребительских упаковках)</th>
									<th>Стоимость</th>
								</tr>
							</thead>
							<tbody> </tbody> 
						</table>
					</text>
				</section>'
	
	--select @XmlTable

	select @XmlTable =(
		--select CAST('' as xml).query('<tr ID="{concat (string(sql:column("Esklp_Trn")), "_" , string(sql:column("Number")))}">
		select CAST('' as xml).query('<tr>
									<td>{sql:column("ekslp_name")}</td>
									<td>{sql:column("Esklp_DoseView")}</td>
									<td>{sql:column("Esklp_MedicationForms")}</td>
									<td>{sql:column("Esklp_MedicamentProducer")}</td>
									<td>{sql:column("Esklp_PackQuantity")}</td>
									<td>{sql:column("Amount")}</td>
									<td>{sql:column("Sum")}</td>
								</tr>')
		from #Recipe_Items
		for xml path('')
		) -- Заменил <tr ID="dis" на строку с concat что бы получить уникальные поля
	
	--select @XmlTable
	
	SET @XmlDop.modify('             
insert sql:variable("@XmlTable")             
into (//tbody)[1]')   
	
	--select @XmlDop
	
	select @Xml_Items = (
	select CAST('' as xml).query(
		'<entry>
						<supply classCode="SPLY" moodCode="EVN">
							<effectiveTime value="{sql:column("DocDate")}"/>
							<quantity value="{sql:column("Amount")}"/>
							<product typeCode="PRD">
								<manufacturedProduct classCode="MANU">
									<manufacturedMaterial classCode="MMAT" determinerCode="KIND">
										<code code="{sql:column("ekslp_code")}" codeSystem="1.2.643.5.1.13.13.99.2.540" codeSystemVersion="{sql:column("ekslp_version")}" displayName="{sql:column("ekslp_name")}" codeSystemName="Лекарственные препараты. Товарные позиции. ЕСКЛП с кодами КТРУ"/>
									</manufacturedMaterial>
								</manufacturedProduct>
							</product>
							<entryRelationship typeCode="COMP">
								<observation classCode="OBS" moodCode="EVN">
									<code code="6015" codeSystem="1.2.643.5.1.13.13.99.2.166" codeSystemVersion="2.1" codeSystemName="Кодируемые поля CDA документов" displayName="Стоимость"/>
									<value xsi:type="REAL" value="{sql:column("Sum")}"/>
								</observation>
							</entryRelationship>
						</supply>
					</entry>')
		from #Recipe_Items
		for xml path(''))
			
	--select @Xml_Items
	
	SET @XmlDop.modify('             
	insert sql:variable("@Xml_Items")             
	into (//section)[1]')   
	
	--select @XmlDop
end
else if(@Type = 'Dispense_Med_Prod')
begin
	set @XmlDop = '
				<section>
					<code code="MEDDISPENSE" codeSystem="1.2.643.5.1.13.13.99.2.197" codeSystemVersion="2.1" codeSystemName="Секции CDA документов" displayName="Отпуск лекарственного препарата/изделия медицинского назначения/специализированного продукта лечебного питания"/>
					<title>Отпуск</title>
					<text>
						<table>
							<thead>
								<tr>
									<th>Наименование</th>
									<th>Количество единиц в упаковке</th>
									<th>Стоимость</th>
								</tr>
							</thead>
							<tbody> </tbody> 
						</table>
					</text>
				</section>'
	
	--select @XmlTable
	
	select @XmlTable =(
		--select CAST('' as xml).query('<tr ID="{concat (string(sql:column("Esklp_Trn")), "_" , string(sql:column("Number")), "_" , string(sql:column("meddev_name")))}">
		select CAST('' as xml).query('<tr>
									<td>{sql:column("meddev_name")}</td>
									<td>{sql:column("Amount")}</td>
									<td>{sql:column("Sum")}</td>
								</tr>')
		from #Recipe_Items
		for xml path('')
		) -- Заменил <tr ID="dis" на строку с concat что бы получить уникальные поля
	
	--select @XmlRows
	
	SET @XmlDop.modify('             
	insert sql:variable("@XmlTable")             
	into (//tbody)[1]')   
	
	--select @XmlDop
	
	select @Xml_Items = (
	select CAST('' as xml).query(
						'declare namespace fias = "urn:hl7-ru:fias";
						<entry>
						<supply classCode="SPLY" moodCode="EVN">
							<effectiveTime value="{sql:column("DocDate")}"/>
							<quantity value="{sql:column("Amount")}"/>
							<product typeCode="PRD">
								<manufacturedProduct classCode="MANU">
									<manufacturedMaterial classCode="MMAT" determinerCode="KIND">
										<code code="{sql:column("meddev_code")}" codeSystem="1.2.643.5.1.13.13.99.2.604" codeSystemVersion="{sql:column("meddev_version")}" displayName="{sql:column("meddev_name")}" codeSystemName="ФРЛЛО. Справочник медицинских изделий согласно каталогу товаров, работ, услуг для обеспечения государственных и муниципальных нужд"/>
									</manufacturedMaterial>
								</manufacturedProduct>
							</product>
							<entryRelationship typeCode="COMP">
								<observation classCode="OBS" moodCode="EVN">
									<code code="6015" codeSystem="1.2.643.5.1.13.13.99.2.166" codeSystemVersion="2.1" codeSystemName="Кодируемые поля CDA документов" displayName="Стоимость"/>
									<value xsi:type="REAL" value="{sql:column("Sum")}"/>
								</observation>
							</entryRelationship>
						</supply>
					</entry>')
		from #Recipe_Items
		for xml path(''))
			
	--select @Xml_Items
	
	SET @XmlDop.modify('             
	insert sql:variable("@Xml_Items")             
	into (//section)[1]')   
	
	--select @XmlDop
end
else if(@Type = 'Dispense_nut_sup')
begin
	set @XmlDop = '
				<section>
					<code code="MEDDISPENSE" codeSystem="1.2.643.5.1.13.13.99.2.197" codeSystemVersion="2.1" codeSystemName="Секции CDA документов" displayName="Отпуск лекарственного препарата/изделия медицинского назначения/специализированного продукта лечебного питания"/>
					<title>Отпуск</title>
					<text>
						<table>
							<thead>
								<tr>
									<th>Торговое наименование</th>
									<th>Форма отпущенного препарата</th>
									<th>Отпущено (в потребительских упаковках)</th>
									<th>Стоимость</th>
								</tr>
							</thead>
							<tbody> </tbody> 
						</table>
					</text>
				</section>'
	
	--select @XmlTable
	
	select @XmlTable =(
		--select CAST('' as xml).query('<tr ID="{concat (string(sql:column("Esklp_Trn")), "_" , string(sql:column("Number")), "_" , string(sql:column("nut_name")))}"> 
		select CAST('' as xml).query('<tr> 
									<td>{sql:column("nut_name")}</td>
									<td>{sql:column("nut_MedicationForms")}</td>
									<td>{sql:column("Amount")}</td>
									<td>{sql:column("Sum")}</td>
								</tr>')
		from #Recipe_Items
		for xml path('')
) -- Заменил <tr ID="dis" на строку с concat что бы получить уникальные поля 
	
	--select @XmlRows
	
	SET @XmlDop.modify('             
insert sql:variable("@XmlTable")             
into (//tbody)[1]')   
	
	--select @XmlDop
				
	select @Xml_Items = (
	select CAST('' as xml).query(
						'declare namespace fias = "urn:hl7-ru:fias";
						<entry>
						<supply classCode="SPLY" moodCode="EVN">
							<effectiveTime value="{sql:column("DocDate")}"/>
							<quantity value="{sql:column("Amount")}"/>
							<product typeCode="PRD">
								<manufacturedProduct classCode="MANU">
									<manufacturedMaterial classCode="MMAT" determinerCode="KIND">
										<code code="{sql:column("nut_code")}" codeSystem="1.2.643.5.1.13.13.99.2.603" codeSystemVersion="{sql:column("nut_version")}" displayName="{sql:column("nut_name")}" codeSystemName="ФРЛЛО. Справочник специализированного питания согласно каталогу товаров, работ, услуг для обеспечения государственных и муниципальных нужд"/>
									</manufacturedMaterial>
								</manufacturedProduct>
							</product>
							<entryRelationship typeCode="COMP">
								<observation classCode="OBS" moodCode="EVN">
									<code code="6015" codeSystem="1.2.643.5.1.13.13.99.2.166" codeSystemVersion="2.1" codeSystemName="Кодируемые поля CDA документов" displayName="Стоимость"/>
									<value xsi:type="REAL" value="{sql:column("Sum")}"/>
								</observation>
							</entryRelationship>
						</supply>
					</entry>')
		from #Recipe_Items
		for xml path(''))
	--select @Xml_Items
	
	SET @XmlDop.modify('             
	insert sql:variable("@Xml_Items")             
	into (//section)[1]')   
	
	--select @XmlDop
end

select @Xml = (
		select CAST('' as xml).query(
		'<ClinicalDocument xmlns="urn:hl7-org:v3" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:fias="urn:hl7-ru:fias" xmlns:identity="urn:hl7-ru:identity" xmlns:address="urn:hl7-ru:address" xmlns:medService="urn:hl7-ru:medService">
		<realmCode code="RU"/>
		<typeId root="2.16.840.1.113883.1.3" extension="POCD_MT000040"/>
		<templateId root="1.2.643.5.1.13.13.14.38.9.4"/>
		<id root="{sql:column("UnicIdDoc51")}" extension="{sql:column("IdDoc")}"/>
		<code code="38" codeSystem="1.2.643.5.1.13.13.11.1522" codeSystemVersion="7.22" codeSystemName="Виды медицинской документации" displayName="Отпуск по рецепту на лекарственный препарат, изделие медицинского назначения и специализированный продукт лечебного питания"/>
		<title>Отпуск по рецепту на лекарственный препарат, изделие медицинского назначения и специализированный продукт лечебного питания</title>
		<effectiveTime value="{sql:column("DocDate")}"/>
		<confidentialityCode code="N" codeSystem="1.2.643.5.1.13.13.99.2.285" codeSystemVersion="1.2" codeSystemName="Уровень конфиденциальности медицинского документа" displayName="Обычный"/>
		<languageCode code="ru-RU"/>
		<setId root="{sql:column("UnicIdDoc50")}" extension="{sql:column("UnicIdVers")}"/>
		<versionNumber value="1"/>
		<Replace/>
		<component>
			<structuredBody>
				<component>
					<section>
						<code code="DOCINFO" codeSystem="1.2.643.5.1.13.13.99.2.197" codeSystemVersion="2.1" codeSystemName="Секции CDA документов" displayName="Сведения о документе"/>
						<title>Сведения о рецепте</title>
						<text>
							<table>
								<caption>Информация о рецепте</caption>
								<tbody>
									<tr>
										<td>
											<content>Серия рецепта</content>
										</td>
										<td>
											<content>{sql:column("Serial")}</content>
										</td>
									</tr>
									<tr>
										<td>
											<content>Номер рецепта</content>
										</td>
										<td>
											<content>{sql:column("Number")}</content>
										</td>
									</tr>
								</tbody>
							</table>
						</text>
						<entry>
							<observation classCode="OBS" moodCode="EVN">
								<code code="6012" codeSystem="1.2.643.5.1.13.13.99.2.166" codeSystemVersion="2.1" codeSystemName="Кодируемые поля CDA документов" displayName="Статус рецепта"/>
								<value xsi:type="BL" value="true"/>
								<entryRelationship typeCode="COMP">
									<observation classCode="OBS" moodCode="EVN">
										<code code="6001" codeSystem="1.2.643.5.1.13.13.99.2.166" codeSystemVersion="2.1" codeSystemName="Кодируемые поля CDA документов" displayName="Серия рецепта"/>
										<value xsi:type="ST">{sql:column("Serial")}</value>
									</observation>
								</entryRelationship>
								<entryRelationship typeCode="COMP">
									<observation classCode="OBS" moodCode="EVN">
										<code code="6002" codeSystem="1.2.643.5.1.13.13.99.2.166" codeSystemVersion="2.1" codeSystemName="Кодируемые поля CDA документов" displayName="Номер рецепта"/>											
										<value xsi:type="ST">{sql:column("Number")}</value>
									</observation>
								</entryRelationship>
								<reference typeCode="REFR">
									<externalDocument>
										<id root="{sql:column("UnicIdDocMo51")}" extension="{sql:column("IdDocMO")}"/>
										<id nullFlavor="NI"/>
										<setId root="{sql:column("UnicIdDocMo50")}" extension="87"/>
									</externalDocument>
								</reference>
							</observation>
						</entry>
					</section>
				</component>
				<component>
				</component>
			</structuredBody>
		</component>
	</ClinicalDocument>')
		from #Recipe
		)

SET @xml.modify (
	'
	declare namespace xmlns1 = "urn:hl7-org:v3";
	declare namespace xsi = "http://www.w3.org/2001/XMLSchema-instance";
	declare namespace fias = "urn:hl7-ru:fias";
	declare namespace identity1 = "urn:hl7-ru:identity";
	declare namespace address = "urn:hl7-ru:address";
	declare namespace medService = "urn:hl7-ru:medService";
	insert sql:variable("@documentationOf")
	after (/xmlns1:ClinicalDocument/xmlns1:Replace)[1]'
	)

SET @xml.modify (
	'
	declare namespace xmlns1 = "urn:hl7-org:v3";
	declare namespace xsi = "http://www.w3.org/2001/XMLSchema-instance";
	declare namespace fias = "urn:hl7-ru:fias";
	declare namespace identity1 = "urn:hl7-ru:identity";
	declare namespace address = "urn:hl7-ru:address";
	declare namespace medService = "urn:hl7-ru:medService";
	insert sql:variable("@legalAuthenticator")
	after (/xmlns1:ClinicalDocument/xmlns1:Replace)[1]
	'
	)

SET @xml.modify (
	'
	declare namespace xmlns1 = "urn:hl7-org:v3";
	declare namespace xsi = "http://www.w3.org/2001/XMLSchema-instance";
	declare namespace fias = "urn:hl7-ru:fias";
	declare namespace identity1 = "urn:hl7-ru:identity";
	declare namespace address = "urn:hl7-ru:address";
	declare namespace medService = "urn:hl7-ru:medService";
	insert sql:variable("@informationRecipient")
	after (/xmlns1:ClinicalDocument/xmlns1:Replace)[1]'
	)	

SET @xml.modify (
	'
	declare namespace xmlns1 = "urn:hl7-org:v3";
	declare namespace xsi = "http://www.w3.org/2001/XMLSchema-instance";
	declare namespace fias = "urn:hl7-ru:fias";
	declare namespace identity1 = "urn:hl7-ru:identity";
	declare namespace address = "urn:hl7-ru:address";
	declare namespace medService = "urn:hl7-ru:medService";
	insert sql:variable("@custodian")
	after (/xmlns1:ClinicalDocument/xmlns1:Replace)[1]
	'
	)
		

SET @xml.modify (
	'
	declare namespace xmlns1 = "urn:hl7-org:v3";
	declare namespace xsi = "http://www.w3.org/2001/XMLSchema-instance";
	declare namespace identity1 = "urn:hl7-ru:identity";
	declare namespace address = "urn:hl7-ru:address";
	declare namespace medService = "urn:hl7-ru:medService";
	insert sql:variable("@author")
	after (/xmlns1:ClinicalDocument/xmlns1:Replace)[1]
	'
	)
	

SET @xml.modify (
	'
	declare namespace xmlns1 = "urn:hl7-org:v3";
	declare namespace xsi = "http://www.w3.org/2001/XMLSchema-instance";
	declare namespace identity1 = "urn:hl7-ru:identity";
	declare namespace address = "urn:hl7-ru:address";
	declare namespace medService = "urn:hl7-ru:medService";
	insert sql:variable("@XmlDop")
	into (/xmlns1:ClinicalDocument/xmlns1:component/xmlns1:structuredBody/xmlns1:component)[2]
	'
	)

If(@delayedTabl is not null)
begin
	SET @xml.modify (
	'
	declare namespace xmlns1 = "urn:hl7-org:v3";
	declare namespace xsi = "http://www.w3.org/2001/XMLSchema-instance";
	declare namespace fias = "urn:hl7-ru:fias";
	declare namespace identity1 = "urn:hl7-ru:identity";
	declare namespace address = "urn:hl7-ru:address";
	declare namespace medService = "urn:hl7-ru:medService";
	insert sql:variable("@delayedTabl")
	into (/xmlns1:ClinicalDocument/xmlns1:component/xmlns1:structuredBody/xmlns1:component/xmlns1:section/xmlns1:text)[1]
	'
	)

	SET @xml.modify (
	'
	declare namespace xmlns1 = "urn:hl7-org:v3";
	declare namespace xsi = "http://www.w3.org/2001/XMLSchema-instance";
	declare namespace identity1 = "urn:hl7-ru:identity";
	declare namespace address = "urn:hl7-ru:address";
	declare namespace medService = "urn:hl7-ru:medService";
	insert sql:variable("@delayedData")
	into (/xmlns1:ClinicalDocument/xmlns1:component/xmlns1:structuredBody/xmlns1:component/xmlns1:section)[1]
	'
	)
end

set @Xml = CAST( REPLACE(CAST(@Xml AS NVARCHAR(MAX)), '<Replace/>', @recordTarget) AS XML)

declare @Header xml
set @Header = Cast(
'<?xml-stylesheet type="text/xsl" href="Dispense.xsl"?>' as xml)

SET @Xml.modify (
	'
	declare namespace xmlns1 = "urn:hl7-org:v3";
	declare namespace xsi = "http://www.w3.org/2001/XMLSchema-instance";
	declare namespace fias = "urn:hl7-ru:fias";
	declare namespace identity1 = "urn:hl7-ru:identity";
	declare namespace address = "urn:hl7-ru:address";
	declare namespace medService = "urn:hl7-ru:medService";
	insert sql:variable("@Header")
	before (/xmlns1:ClinicalDocument)[1]
	'
	)
	
select @Result = Replace(Cast(@Xml as varchar(max)), ' xmlns=""', '')

END