IF EXISTS (SELECT * FROM sys.objects WHERE [type] IN (N'P', N'PC') AND OBJECT_ID = OBJECT_ID(N'[HM].[NewProductAsXML]'))
DROP PROCEDURE [HM].[NewProductAsXML];
GO

-- Procedura umożliwiająca pobranie danych towaru w postaci XML
CREATE PROCEDURE [HM].[NewProductAsXML] (@catalogId INT, @kindId INT, @productData XML)
AS
BEGIN
	SET NOCOUNT ON; -- Stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set.
	SET XACT_ABORT ON; -- Specifies whether SQL Server automatically rolls back the current transaction when a Transact-SQL statement raises a run-time error.
	
BEGIN TRY

		IF	0 = ISNULL(@catalogId, 0)
			SET @catalogId = 2200;

		IF	0 = ISNULL(@kindId, 0)
			SET @kindId = (SELECT t.id FROM HM.TW t WHERE t.id = 3200);


		DECLARE @changes TABLE (
			[Catalog] INT,
			[Kind] INT,
			[Type] INT,
			[CustomMeasureUnit] NVARCHAR(50),
			[TypeMeasureUnit] NVARCHAR(50),
			[ValueVAT] INT,
			[CodeCN] NVARCHAR(50),
			[GroupJPK] NVARCHAR(50),
			[CodePKWIU] NVARCHAR(50),
			[Marker] INT,
			[SalePriceTranslation] INT,
			[WarehouseSettlement] NVARCHAR(6),
			[PriceNegotiation] NVARCHAR(20),
			[PostingParameter] NVARCHAR(50),
			[PriceRounding] INT,
			[BasePriceNetto] FLOAT,
			[BasePriceCurrency] NVARCHAR(50),
			[BuyPrice] FLOAT,
			[BuyDate] DATETIME,
			[BuyPriceNotInPLN] FLOAT,
			[BuyCurrency] NVARCHAR(50),
			[NoEditOfSet] NVARCHAR(10),
			[PriceOfSetCalculatedBasedOfElements] NVARCHAR(10),
			[ElementsOfSetShownOnSaleStatement] NVARCHAR(10)
		 );

		 INSERT INTO @changes
			SELECT
			@catalogId,
			@kindId,
			p.[subtypext] AS [Type],
			IIF(
				ISNULL((SELECT cena FROM HM.ZZ WHERE typ=32 AND baza1=20 AND id2=1235), 0)!=2,
				p.[jm],
				CASE 
					WHEN p.[jmdef]=252 THEN ISNULL(IIF(p.[jmdod2]='', p.[jm], p.[jmdod2]), p.[jm])
					WHEN p.[jmdef]=253 THEN ISNULL(IIF(p.[jmdod1]='', p.[jm], p.[jmdod1]), p.[jm])
					--TODO implementacja oznaczen jednostek dodatkowych 3-10
					ELSE p.[jm] 
				END
			) AS [CustomMeasureUnit],
			IIF(
				ISNULL((SELECT cena FROM HM.ZZ WHERE typ=32 AND baza1=20 AND id2=1235), 0)!=2,
				'ewidencyjna',
				'domyślna'
			) AS [TypeMeasureUnit],
			p.[vatsp] AS [ValueVAT],
			p.[pcn] AS [CodeCN],
			p.[JPK_V7] AS [GroupJPK],
			p.[sww] AS [CodePKWIU],
			p.[znacznik] AS [Marker],
			p.[przelicz] AS [SalePriceTranslation],
			IIF(p.[metoda] = 0,'FIFO','LIFO') AS [WarehouseSettlement],
			IIF(p.[negoc] = 1,'True','False') AS [PriceNegotiation],
			p.[typks] AS [PostingParameter],
			p.[zaokrag] AS [PriceRounding],
			ISNULL((SELECT TOP(1) cena FROM [HM].[CN] WHERE idpm = @kindId AND typ = 8 and aktywna = 1),0) AS [BasePriceNetto],
			ISNULL((SELECT TOP(1) waluta FROM [HM].[CN] WHERE idpm = @kindId AND typ = 8 and aktywna = 1),0) AS [BasePriceCurrency],
			ISNULL((SELECT TOP(1) cena FROM [HM].[CN] WHERE idpm = @kindId AND typ = 6 and aktywna = 1),0) AS [BuyPrice],
			(SELECT TOP(1) datastart FROM [HM].[CN] WHERE idpm = @kindId AND typ = 6 and aktywna = 1) AS [BuyDate],
			ISNULL((SELECT TOP(1) cena FROM [HM].[CN] WHERE idpm = @kindId AND typ = 7 and aktywna = 1),0) AS [BuyPriceNotInPLN],
			ISNULL((SELECT TOP(1) waluta FROM [HM].[CN] WHERE idpm = @kindId AND typ = 7 and aktywna = 1),'') AS [BuyCurrency],
			ISNULL(IIF((flag & 16384) = 16384 AND p.[subtyp]=3, 'True', 'False'),'False') AS [NoEditOfSet], 
			ISNULL(IIF((flag & 32768) = 32768 OR p.[subtyp]!=3, 'True', 'False'),'False') AS [PriceOfSetCalculatedBasedOfElements], 
			ISNULL(IIF((flag & 8192) = 8192 AND p.[subtyp]=3, 'True', 'False'),'False') AS [ElementsOfSetShownOnSaleStatement]
		FROM [HM].[TW] p
		WHERE p.id = @kindId

		DECLARE @tempPrices XML
		DECLARE @prices varchar(MAX)
		SET @tempPrices =( SELECT 
			(
			  ISNULL((SELECT 
			  ISNULL(cn.[typ],spt.[Id]) AS [PriceTypeId]
			  ,ISNULL(spt.[Symbol],'') AS [Type]
			  ,ISNULL(spt.[Name],'') AS [Name]
			  ,ISNULL(cn.[waluta],'') AS [Currency]
			  ,0 AS [ProductId]
			  ,0 AS [Overhead]
			  ,ISNULL(cn.[parametr],0) AS [Margin]
			  ,ISNULL(cn.[cena],0) AS [SalePrice]
			  ,ISNULL(IIF(spt.[IsGross] = 1,'Brutto','Netto'),'') AS [Brutto]
			  ,IIF(cn.[flagcen] = 1,'True','False') AS [IsActive]			  
			  FROM [HM].[SellingPriceTypes] spt 
			  LEFT JOIN [HM].[CN] cn ON  cn.typ = spt.Id AND cn.idpm = @kindId AND cn.idpd IS NULL and cn. aktywna = 1 and cn.idck is NULL
			  WHERE spt.IsActive != 0
				FOR XML PATH('PriceType'), ELEMENTS XSINIL, TYPE), '')
			) AS [PriceTypes]
		FROM [HM].[TW] p
		WHERE p.id = @kindId
		FOR XML PATH(''));
		SET @prices = CAST (@tempPrices AS varchar(MAX))
		
		DECLARE @tempElements XML
		DECLARE @elements varchar(MAX)
		SET @tempElements =( SELECT 
			(ISNULL((SELECT 
				  [id1] as [ProductId],
				  [id2] as [ProductPartId],
				  [ilosc] as [Amount],
				  tw.jm as [UnitOfMeasure]
			  FROM [HM].[ZZ] zz
			  JOIN [HM].TW tw ON zz.id2 = tw.id
			  WHERE id1 = @kindId
				FOR XML PATH('SetElement'), ELEMENTS XSINIL, TYPE), '')
			) AS [SetElements]
		FOR XML PATH(''));
		SET @elements = CAST (@tempElements AS varchar(MAX))

		--jednostki miary
		DECLARE @tempUom XML
		DECLARE @uom varchar(MAX)
		SET @tempUom =( SELECT 
			(
			  ISNULL((SELECT * FROM (
				SELECT 
					-1 as Id, 
					0 as TwId, 
					'Ewidencyjna' as [Name],
					'00000000-0000-0000-0000-000000000000' as UnitId, 
					p.jm as Unit, 
					1 as Multiplier, 
					'True' as IsDefault, 
					1 as IsBuiltIn, 
					0 as [Order]
					FROM HM.TW p WHERE p.id=@kindId
				UNION ALL
				SELECT -2, 0, 'Dodatkowa1', '11111111-1111-1111-1111-111111111111', '', 1, 'False', 1, 1
				UNION ALL
				SELECT -3, 0, 'Dodatkowa2', '22222222-2222-2222-2222-222222222222', '', 1, 'False', 1, 1
				UNION ALL
				SELECT 0, 0, el.Shortcut, el.[Guid], '', 1, 'False', 0, CAST(att.StringValue AS INT) FROM SSCommon.STElements el
				INNER JOIN SSCommon.STElementKinds ek on ek.ElementKindId=el.ElementKindId
				INNER JOIN SSCommon.STElementExAttributes att on att.ElementId=el.ElementId and att.[Name]='Kolejność'
				WHERE ek.Shortcut='Jednostki dodatkowe'
				) units ORDER BY IsBuiltIn DESC, [Order]
				FOR XML PATH('UnitOfMeasure'), ELEMENTS XSINIL, TYPE), '')
			) AS [UnitsOfMeasure]
		FOR XML PATH(''));
		SET @uom = CAST (@tempUom AS varchar(MAX))

		--pola logistyczne
		DECLARE @tempLogisticFields XML
		DECLARE @logisticFields varchar(MAX)
		SET @tempLogisticFields =( SELECT 
			(
			  ISNULL((SELECT * FROM (
					SELECT 
						0 as [Id],
						uomd.Guid as [UnitTypeGuid],
						uomd.[Name] as [UnitTypeName],
						lfd.Guid as [LogisticFieldGuid],
						lfd.[Name] as [LogisticFieldName],
						0 as [Value],
						'' as [Unit],
						ISNULL(lfd.[Order],999) as [FieldOrder], -- do sortowania
						ISNULL(uomd.[Order],999) as [UnitOrder] -- do sortowania
					FROM (
						SELECT el.Shortcut as [Name], el.[Guid], CAST(att.StringValue AS INT) as [Order] FROM SSCommon.STElements el
						INNER JOIN SSCommon.STElementKinds ek on ek.ElementKindId=el.ElementKindId
						LEFT JOIN SSCommon.STElementExAttributes att on att.ElementId=el.ElementId and att.[Name]='Kolejność'
						WHERE ek.Shortcut='Dane logistyczne'
					) lfd
					CROSS JOIN (
						SELECT el.Shortcut as [Name], el.[Guid], CAST(att.StringValue AS INT) as [Order] FROM SSCommon.STElements el
						INNER JOIN SSCommon.STElementKinds ek on ek.ElementKindId=el.ElementKindId
						LEFT JOIN SSCommon.STElementExAttributes att on att.ElementId=el.ElementId and att.[Name]='Kolejność'
						WHERE ek.Shortcut='Jednostki dodatkowe'
						UNION
						SELECT 'Ewidencyjna', '00000000-0000-0000-0000-000000000000', 0
						UNION
						SELECT 'Dodatkowa1', '11111111-1111-1111-1111-111111111111', 1
						UNION
						SELECT 'Dodatkowa2', '22222222-2222-2222-2222-222222222222', 2
					) uomd
				) lf ORDER BY UnitOrder, FieldOrder
				FOR XML PATH('LogisticField'), ELEMENTS XSINIL, TYPE), '')
			) AS [LogisticFields]
		FOR XML PATH(''));
		SET @logisticFields = CAST (@tempLogisticFields AS varchar(MAX))

		--kody paskowe
		DECLARE @tempBarCodes XML
		DECLARE @barCodes varchar(MAX)
		SET @tempBarCodes =( SELECT 
			(
			  ISNULL((SELECT 
					-1 as [Id],
					'00000000-0000-0000-0000-000000000000' as [UnitTypeGuid],
					'Ewidencyjna' as [UnitTypeName],
					'' as [BarCodeValue]
				FOR XML PATH('BarCode'), ELEMENTS XSINIL, TYPE), '')
			) AS [BarCodes]
		FOR XML PATH(''));
		SET @barCodes = CAST (@tempBarCodes AS varchar(MAX))

		DECLARE @x TABLE ([value] xml);
		INSERT INTO @x VALUES (@productData);

		IF 1 = (SELECT COUNT(*) FROM @changes)
		BEGIN

			UPDATE @x SET value.modify('delete /Product/Kind');
			UPDATE @x SET value.modify('insert <Kind>{sql:column("Kind")}</Kind> as first into (/Product)[1]') FROM @changes;

			UPDATE @x SET value.modify('delete /Product/Catalog');
			UPDATE @x SET value.modify('insert <Catalog>{sql:column("Catalog")}</Catalog> as first into (/Product)[1]') FROM @changes;

			UPDATE @x SET value.modify('delete /Product/Type');
			UPDATE @x SET value.modify('insert <Type>{sql:column("Type")}</Type> as first into (/Product)[1]') FROM @changes;

			UPDATE @x SET value.modify('delete /Product/CustomMeasureUnit');
			UPDATE @x SET value.modify('insert <CustomMeasureUnit>{sql:column("CustomMeasureUnit")}</CustomMeasureUnit> as first into (/Product)[1]') FROM @changes;

			UPDATE @x SET value.modify('delete /Product/TypeMeasureUnit');
			UPDATE @x SET value.modify('insert <TypeMeasureUnit>{sql:column("TypeMeasureUnit")}</TypeMeasureUnit> as first into (/Product)[1]') FROM @changes;

			UPDATE @x SET value.modify('delete /Product/ValueVAT');
			UPDATE @x SET value.modify('insert <ValueVAT>{sql:column("ValueVAT")}</ValueVAT> as first into (/Product)[1]') FROM @changes;

			UPDATE @x SET value.modify('delete /Product/CodeCN');
			UPDATE @x SET value.modify('insert <CodeCN>{sql:column("CodeCN")}</CodeCN> as first into (/Product)[1]') FROM @changes;

			UPDATE @x SET value.modify('delete /Product/GroupJPK');
			UPDATE @x SET value.modify('insert <GroupJPK>{sql:column("GroupJPK")}</GroupJPK> as first into (/Product)[1]') FROM @changes;

			UPDATE @x SET value.modify('delete /Product/CodePKWIU');
			UPDATE @x SET value.modify('insert <CodePKWIU>{sql:column("CodePKWIU")}</CodePKWIU> as first into (/Product)[1]') FROM @changes;

			UPDATE @x SET value.modify('delete /Product/Marker');
			UPDATE @x SET value.modify('insert <Marker>{sql:column("Marker")}</Marker> as first into (/Product)[1]') FROM @changes;

			UPDATE @x SET value.modify('delete /Product/SalePriceTranslation');
			UPDATE @x SET value.modify('insert <SalePriceTranslation>{sql:column("SalePriceTranslation")}</SalePriceTranslation> as first into (/Product)[1]') FROM @changes;

			UPDATE @x SET value.modify('delete /Product/WarehouseSettlement');
			UPDATE @x SET value.modify('insert <WarehouseSettlement>{sql:column("WarehouseSettlement")}</WarehouseSettlement> as first into (/Product)[1]') FROM @changes;

			UPDATE @x SET value.modify('delete /Product/PriceNegotiation');
			UPDATE @x SET value.modify('insert <PriceNegotiation>{sql:column("PriceNegotiation")}</PriceNegotiation> as first into (/Product)[1]') FROM @changes;

			UPDATE @x SET value.modify('delete /Product/PostingParameter');
			UPDATE @x SET value.modify('insert <PostingParameter>{sql:column("PostingParameter")}</PostingParameter> as first into (/Product)[1]') FROM @changes;

			UPDATE @x SET value.modify('delete /Product/PriceRounding');
			UPDATE @x SET value.modify('insert <PriceRounding>{sql:column("PriceRounding")}</PriceRounding> as first into (/Product)[1]') FROM @changes;

			UPDATE @x SET value.modify('delete /Product/BasePriceNetto');
			UPDATE @x SET value.modify('insert <BasePriceNetto>{sql:column("BasePriceNetto")}</BasePriceNetto> as first into (/Product)[1]') FROM @changes;

			UPDATE @x SET value.modify('delete /Product/BasePriceCurrency');
			UPDATE @x SET value.modify('insert <BasePriceCurrency>{sql:column("BasePriceCurrency")}</BasePriceCurrency> as first into (/Product)[1]') FROM @changes;
			
			UPDATE @x SET value.modify('delete /Product/BuyPrice');
			UPDATE @x SET value.modify('insert <BuyPrice>{sql:column("BuyPrice")}</BuyPrice> as first into (/Product)[1]') FROM @changes;

			UPDATE @x SET value.modify('delete /Product/BuyDate');
			UPDATE @x SET value.modify('insert <BuyDate>{sql:column("BuyDate")}</BuyDate> as first into (/Product)[1]') FROM @changes;

			UPDATE @x SET value.modify('delete /Product/BuyPriceNotInPLN');
			UPDATE @x SET value.modify('insert <BuyPriceNotInPLN>{sql:column("BuyPriceNotInPLN")}</BuyPriceNotInPLN> as first into (/Product)[1]') FROM @changes;

			UPDATE @x SET value.modify('delete /Product/BuyCurrency');
			UPDATE @x SET value.modify('insert <BuyCurrency>{sql:column("BuyCurrency")}</BuyCurrency> as first into (/Product)[1]') FROM @changes;

			UPDATE @x SET value.modify('delete /Product/NoEditOfSet');
			UPDATE @x SET value.modify('insert <NoEditOfSet>{sql:column("NoEditOfSet")}</NoEditOfSet> as first into (/Product)[1]') FROM @changes;

			UPDATE @x SET value.modify('delete /Product/PriceOfSetCalculatedBasedOfElements');
			UPDATE @x SET value.modify('insert <PriceOfSetCalculatedBasedOfElements>{sql:column("PriceOfSetCalculatedBasedOfElements")}</PriceOfSetCalculatedBasedOfElements> as first into (/Product)[1]') FROM @changes;

			UPDATE @x SET value.modify('delete /Product/ElementsOfSetShownOnSaleStatement');
			UPDATE @x SET value.modify('insert <ElementsOfSetShownOnSaleStatement>{sql:column("ElementsOfSetShownOnSaleStatement")}</ElementsOfSetShownOnSaleStatement> as first into (/Product)[1]') FROM @changes;
		END
		
		DECLARE @y nvarchar(MAX)

		UPDATE @x SET value = (SELECT CAST(
		REPLACE(REPLACE(REPLACE(CAST([value] as nvarchar(MAX)),'PriceTypes',CAST(@prices as nvarchar(MAX))),'<<','<'),'>/>', '>')
		AS xml) from @x)
		
		UPDATE @x SET value = (SELECT CAST(
		REPLACE(REPLACE(REPLACE(CAST([value] as nvarchar(MAX)),'SetElements',CAST(@elements as nvarchar(MAX))),'<<','<'),'>/>', '>')
		AS xml) from @x)
		
		UPDATE @x SET value = (SELECT CAST(
		REPLACE(REPLACE(REPLACE(CAST([value] as nvarchar(MAX)),'UnitsOfMeasure',CAST(@uom as nvarchar(MAX))),'<<','<'),'>/>', '>')
		AS xml) from @x)

		UPDATE @x SET value = (SELECT CAST(
		REPLACE(REPLACE(REPLACE(CAST([value] as nvarchar(MAX)),'<LogisticFields/>',CAST(@logisticFields as nvarchar(MAX))),'<<','<'),'>/>', '>')
		AS xml) from @x)

		UPDATE @x SET value = (SELECT CAST(
		REPLACE(REPLACE(REPLACE(CAST([value] as nvarchar(MAX)),'<BarCodes/>',CAST(@barCodes as nvarchar(MAX))),'<<','<'),'>/>', '>')
		AS xml) from @x)

		SELECT [value].query('/Product')
		from @x FOR XML PATH('');
	END TRY
	BEGIN CATCH
		THROW;
	END CATCH
END
