DROP PROCEDURE IF EXISTS pharm.sp_BarcodeTemplateLoad;

CREATE PROCEDURE pharm.sp_BarcodeTemplateLoad
(
	"id" uuid DEFAULT NULL::uuid,
	"Xml" text DEFAULT NULL
)
    language plpgsql
as
$$
Begin
	IF ("Xml" is null)
	then
		select
		XMLELEMENT
		(
			name "BarcodeTemplate"
			,XMLFOREST
			(
				 bt.BarcodeTemplateID													as "BarcodeTemplateID"
				,bt.Entity																as "Entity"
				,to_char(bt.BeginDate, 'DDMMYYYY')										as "BeginDate"
				,to_char(bt.EndDate, 'DDMMYYYY')										as "EndDate"
				,bt.Version																as "Version"
				,bt.TemplateName														as "TemplateName"
				,bt.DataFormat															as "DataFormat"
			),(
				select
				xmlagg
				(
					xmlelement
					(
						name "BarcodeTemplateAttribute"
						,xmlforest
						(
							 bta.BarcodeTemplateAttributeID									as "BarcodeTemplateAttributeID"
							,bta.Order													    as "Order"
							,bta.Size														as "Size"
							,bta.Description												as "Description"
							,bta.LexemPathLink												as "LexemPathLink"
							,bta.ValueType													as "ValueType"
							,bta.Name														as "Name"
							,bta.BarcodeTemplate											as "BarcodeTemplate"
							,bta.BarcodeTemplateAttribute									as "BarcodeTemplateAttribute"
						),(
								select
								xmlagg
								(
									xmlelement
									(
										name "AdditionalBarcodeTemplate"
										,xmlforest
										(
											 aba.AdditionalBarcodeAttributeID						as "AdditionalBarcodeAttributeID"
											,aba.Name												as "Name"
											,aba.Lexem												as "Lexem"
											,aba.BarcodeTemplateAttribute							as "BarcodeTemplateAttribute"
										)
									)
								)
								from PHARM.T_ADDITIONAL_BARCODE_ATTRIBUTE aba
								where aba.BarcodeTemplateAttribute = bta.BarcodeTemplateAttributeID
							)		
						)
					)
					FROM PHARM.T_BARCODE_TEMPLATE_ATTRIBUTE bta
					where bta.BarcodeTemplate = bt.BarcodeTemplateID
				)
			)
			FROM PHARM.T_BARCODE_TEMPLATE bt
			into "Xml"
			where bt.BarcodeTemplateID = "id";
	ELSE
		-- Generate table #T_BARCODE_TEMPLATE
		Drop Table If Exists BARCODE_TEMPLATE;
		Create Temp Table BARCODE_TEMPLATE
		as
		SELECT	(xpath('/BarcodeTemplateID[1]/text()', t.x))[1]::text::uuid as BarcodeTemplateID
		,		(xpath('/Entity[1]/text()', t.x))[1]::text::integer as Entity
		,		to_timestamp((xpath('/BeginDate[1]/text()', x))[1]::varchar(10), 'DDMMYYYY')::text::int as BeginDate	
		,		to_timestamp((xpath('/EndDate[1]/text()', x))[1]::varchar(10), 'DDMMYYYY')::text::int as EndDate
		,		(xpath('/Version[1]/text()', t.x))[1]::text::integer as Version
		,		(xpath('/TemplateName[1]/text()', t.x))[1]::text::varchar(255) as TemplateName
		,		(xpath('/DataFormat[1]/text()', t.x))[1]::text::integer as DataFormat
		from
		(
			select unnest(xpath('/BarcodeTemplate', "Xml"::xml)) x
		) t;

		-- INSERT PHARM.T_BARCODE_TEMPLATE
		INSERT INTO PHARM.T_BARCODE_TEMPLATE
		(	BarcodeTemplateID,
			Version, 
			Entity, 
			TemplateName, 
			DataFormat, 
			BeginDate, 
			EndDate)
		SELECT
			bt.BarcodeTemplateID
			,bt.Version
			,bt.Entity
			,bt.TemplateName
			,bt.DataFormat
			,bt.BeginDate
			,bt.EndDate
		from BARCODE_TEMPLATE bt
		where not exists (
				select *
				from PHARM.T_BARCODE_TEMPLATE bt1
				where bt.BarcodeTemplateID = bt1.BarcodeTemplateID 
				);

		Drop Table If Exists BARCODE_TEMPLATE_ATTRIBUTE;
		Create Temp Table BARCODE_TEMPLATE_ATTRIBUTE
		as
		SELECT	(xpath('/BarcodeTemplateAttributeID[1]/text()', t.x))[1]::text::uuid as BarcodeTemplateAttributeID
		,		(xpath('/Order[1]/text()', t.x))[1]::text::integer as Order
		,		(xpath('/Size[1]/text()', t.x))[1]::text::integer as Size
		,		(xpath('/Description[1]/text()', t.x))[1]::text::varchar(255) as Description
		,		(xpath('/LexemPathLink[1]/text()', t.x))[1]::text as LexemPathLink
		,		(xpath('/ValueType[1]/text()', t.x))[1]::text::integer as ValueType
		,		(xpath('/Name[1]/text()', t.x))[1]::text::varchar(255) as Name
		,		(xpath('/BarcodeTemplate[1]/text()', t.x))[1]::text::uuid as BarcodeTemplate
		,		(xpath('/BarcodeTemplateAttribute[1]/text()', t.x))[1]::text::uuid as BarcodeTemplateAttribute
		from
		(
			select unnest(xpath('/BarcodeTemplate/BarcodeTemplateAttribute', "Xml"::xml)) x
		) t;

		-- INSERT PHARM.T_BARCODE_TEMPLATE_ATTRIBUTE
		INSERT INTO PHARM.T_BARCODE_TEMPLATE_ATTRIBUTE
			(BarcodeTemplateAttributeID, "Order", Size, Description, LexemPathLink, ValueType, Name, BarcodeTemplate)
		SELECT
			bta.BarcodeTemplateAttributeID
			,bta.Order
			,bta.Size
			,bta.Description
			,bta.LexemPathLink
			,bta.ValueType
			,bta.Name
			,bta.BarcodeTemplate
		from BARCODE_TEMPLATE_ATTRIBUTE bta
		where not exists (
					select *
					from PHARM.T_BARCODE_TEMPLATE_ATTRIBUTE bta1
					where bta.BarcodeTemplateAttributeID = bta1.BarcodeTemplateAttributeID
				) and bta.BarcodeTemplateAttribute is null;

		INSERT INTO PHARM.T_BARCODE_TEMPLATE_ATTRIBUTE
			(BarcodeTemplateAttributeID, "Order", Size, Description, LexemPathLink, ValueType, Name, BarcodeTemplate, BarcodeTemplateAttribute)
		SELECT
			bta.BarcodeTemplateAttributeID
			,bta.Order
			,bta.Size
			,bta.Description
			,bta.LexemPathLink
			,bta.ValueType
			,bta.Name
			,bta.BarcodeTemplate
			,bta.BarcodeTemplateAttribute
		from BARCODE_TEMPLATE_ATTRIBUTE bta
		where not exists (
					select *
					from PHARM.T_BARCODE_TEMPLATE_ATTRIBUTE bta1
					where bta.BarcodeTemplateAttributeID = bta1.BarcodeTemplateAttributeID
				) and bta.BarcodeTemplateAttribute is not null;

		-- Generate table ADDITIONAL_BARCODE_ATTRIBUTE
		Drop Table If Exists ADDITIONAL_BARCODE_ATTRIBUTE;
		Create Temp Table ADDITIONAL_BARCODE_ATTRIBUTE
		as
		SELECT	(xpath('/AdditionalBarcodeAttributeID[1]/text()', t.x))[1]::text::uuid as AdditionalBarcodeAttributeID1
		,		(xpath('/Name[1]/text()', t.x))[1]::text::varchar(255) as Name
		,		(xpath('/Lexem[1]/text()', t.x))[1]::text as Lexem
		,		(xpath('/BarcodeTemplateAttribute[1]/text()', t.x))[1]::text::uuid as BarcodeTemplateAttribute
		from
		(
			select unnest(xpath('/BarcodeTemplate/BarcodeTemplateAttribute/AdditionalBarcodeTemplate', "Xml"::xml)) x
		) t;

		-- INSERT PHARM.T_ADDITIONAL_BARCODE_ATTRIBUTE
		INSERT INTO PHARM.T_ADDITIONAL_BARCODE_ATTRIBUTE
			(AdditionalBarcodeAttributeID, Name, Lexem, BarcodeTemplateAttribute)
		SELECT
			 aba.AdditionalBarcodeAttributeID
			,aba.Name
			,aba.Lexem
			,aba.BarcodeTemplateAttribute
		from ADDITIONAL_BARCODE_ATTRIBUTE aba
		where not exists (
				select *
				from PHARM.T_ADDITIONAL_BARCODE_ATTRIBUTE aba1
				where aba.AdditionalBarcodeAttributeID = aba1.AdditionalBarcodeAttributeID
			);

		-- Drop table
		DROP TABLE BARCODE_TEMPLATE;
		DROP TABLE BARCODE_TEMPLATE_ATTRIBUTE;
		DROP TABLE ADDITIONAL_BARCODE_ATTRIBUTE;
	END IF;
End;
$$;