DECLARE @sql  NVARCHAR (MAX)
DECLARE @dbName NVARCHAR(255); 
DECLARE @userName NVARCHAR(128);
DECLARE @metasql  NVARCHAR (MAX)
DECLARE db_cursor CURSOR FOR
SELECT name 
FROM sys.databases 
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')
  AND state_desc = 'ONLINE';

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @dbName;
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @sql = N'IF EXISTS (SELECT * FROM sys.objects WHERE [type] IN (N''P'', N''PC'') AND OBJECT_ID = OBJECT_ID(N''[Common].[NewContractorAsXML]''))
					DROP PROCEDURE [Common].[NewContractorAsXML];'

	SET @metasql = 'USE ['+@dbName+']  EXEC (''' + REPLACE(@sql, '''', '''''') + ''')'
	
    EXEC (@metasql)
 
	SET @sql =N'
	CREATE PROCEDURE [Common].[NewContractorAsXML] (@catalogId INT, @kindId INT, @contractorData XML, @moduleCode NVARCHAR(255), @userid int)
	AS
	BEGIN
		BEGIN TRY
	
			IF	0 = ISNULL(@catalogId, 0)
				SET @catalogId = 2100;
	
			IF	0 = ISNULL(@kindId, 0)
				SELECT @kindId = e.ElementId FROM HM.KH k JOIN SSCommon.STElements e on e.ElementId = k.kod WHERE k.id = 3100;
	
			DECLARE @TYPcnUPUSTKH  INT = 15; 
			DECLARE	@znacznikiKH INT =  0; 
	
			SELECT @znacznikiKH = [ElementKindId] 
			  FROM [SSCommon].[STElementKinds] 
			 WHERE [Guid]=''4EFBA075-F67F-427A-BB3A-F98CEAF63101'';	
			
			DECLARE @changes TABLE (
			[PostalCode] NVARCHAR(10) COLLATE database_default,
			[Place] NVARCHAR(50) COLLATE database_default,
			[Province] NVARCHAR(50) COLLATE database_default,
			[Country] NVARCHAR(50) COLLATE database_default,
			[PaymentFormId] INT,
			[PayRegistryId] INT,
			[SaleKindId] INT,
			[PriceTypeId] INT,
			[AccountantParameter] NVARCHAR(20) COLLATE database_default,
			[IsLimitActive] NVARCHAR(6) COLLATE database_default,
			[LimitCurrency] NVARCHAR(20) COLLATE database_default,
			[LimitValue] FLOAT,
			[PriceNegotiation] NVARCHAR(6) COLLATE database_default,
			[DiscountPercent] FLOAT,
			[CatalogId] INT,
			[ContractorKindId] INT,
			[MarkerId] INT
			);

			DECLARE @selectChanges NVARCHAR(MAX), @selectStatement NVARCHAR(MAX), @selectChanges1 NVARCHAR(MAX), @selectChanges2 NVARCHAR(MAX), @selectChanges3 NVARCHAR(MAX), @selectChanges4 NVARCHAR(MAX), @selectChanges5 NVARCHAR(MAX)

			SET @selectChanges1='' ISNULL(k.[kodpocz],'''') AS [PostalCode],
					ISNULL(k.[miejscowosc],'''') AS [Place],
					ISNULL(k.[rejon],'''') AS [Province],
					ISNULL((SELECT [Shortcut] FROM SSCommon.[STElements] WHERE [ElementId] = k.[idKraju]), '''') AS [Country],
					ISNULL(k.[formaplatn],0) AS [PaymentFormId],
					ISNULL(k.[plattyp],0) AS [PayRegistryId],
					(CASE k.[grupacen] WHEN 0 THEN 1 WHEN 1 THEN 2 ELSE 0 END) AS [SaleKindId],
					ISNULL(k.[typceny],0) AS [PriceTypeId],
					ISNULL(k.[typks],'''') AS [AccountantParameter],
					IIF(k.[limit] = 0, ''''false'''', ''''true'''') AS [IsLimitActive],
					ISNULL(k.[limitwaluta],'''') AS [LimitCurrency],
					CAST(ISNULL(k.[limitkwota],0) AS MONEY) AS [LimitValue],
					IIF(k.[negoc] = 0, ''''false'''', ''''true'''') AS [PriceNegotiation],
					CAST(ISNULL((SELECT c.[cena] FROM [HM].[CN] c WHERE c.[idpd] = k.[kod] and c.[typpd] = 1 and c.[typ] =''
					
			SET @selectChanges2=		''), 0) AS MONEY) AS [DiscountPercent],''
					
			SET @selectChanges3=		'' AS [CatalogId],''
					
			SET @selectChanges4=		'' AS [ContractorKindId],
					(SELECT [ElementId] FROM [SSCommon].[STElements] WHERE [ElementKindId] =''
					 
			SET @selectChanges5=		'' AND [Shortcut] = CAST(ISNULL(k.[znacznik],0) AS NVARCHAR(100))) AS [MarkerId]
				FROM [HM].[KH] k ''
				

			SET @selectChanges = CONCAT (@selectChanges1 ,@TYPcnUPUSTKH,@selectChanges2,@catalogId,@selectChanges3,@kindId,@selectChanges4,@znacznikiKH,@selectChanges5)
			
			SET @selectStatement = CONCAT (''SELECT'', @selectChanges, ''WHERE k.[typ] = 110 AND k.[kod] ='', @kindId)
			INSERT INTO @changes EXEC(@selectStatement)
    
			IF 0 = (SELECT COUNT(*) FROM @changes)
			BEGIN
				SET @selectStatement = CONCAT (''SELECT TOP(1)'', @selectChanges, 
					''INNER JOIN [SSCommon].[STElements] el ON [ElementId] ='', @kindId,
						'' INNER JOIN [SSCommon].[STElementKinds] ek ON ek.ElementKindId = el.ElementKindId
					WHERE k.[typ] = 110 AND ek.Guid = ''''0CE26851-5729-4533-99B0-37E08B06D68B'''''')
			INSERT INTO @changes EXEC(@selectStatement)
			END

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

			IF 1 = (SELECT COUNT(*) FROM @changes)
			BEGIN
			if (SELECT [PostalCode] FROM @changes) <> ''''
				BEGIN
					UPDATE @x SET value.modify(''delete /Contractor/MainAddress/PostalCode'');
					UPDATE @x SET value.modify(''insert <PostalCode>{sql:column("PostalCode")}</PostalCode> as first into (/Contractor/MainAddress)[1]'') FROM @changes;
				END
				
				if (SELECT [Place] FROM @changes) <> ''''
				BEGIN
					UPDATE @x SET value.modify(''delete /Contractor/MainAddress/Place'');
					UPDATE @x SET value.modify(''insert <Place>{sql:column("Place")}</Place> as first into (/Contractor/MainAddress)[1]'') FROM @changes;
				END
				
				if (SELECT [Province] FROM @changes) <> ''''
				BEGIN
					UPDATE @x SET value.modify(''delete /Contractor/MainAddress/Province'');
					UPDATE @x SET value.modify(''insert <Province>{sql:column("Province")}</Province> as first into (/Contractor/MainAddress)[1]'') FROM @changes;
				END
				
				if (SELECT [Country] FROM @changes) <> ''''
				BEGIN
					UPDATE @x SET value.modify(''delete /Contractor/MainAddress/Country'');
					UPDATE @x SET value.modify(''insert <Country>{sql:column("Country")}</Country> as first into (/Contractor/MainAddress)[1]'') FROM @changes;
				END
				
				if (SELECT [PaymentFormId] FROM @changes) <> 0
				BEGIN
					UPDATE @x SET value.modify(''delete /Contractor/PaymentFormId'');
					UPDATE @x SET value.modify(''insert <PaymentFormId>{sql:column("PaymentFormId")}</PaymentFormId> as first into (/Contractor)[1]'') FROM @changes;
					DECLARE @days INT
					SET @days = ISNULL((SELECT [long] FROM [HM].[PaymentForm] pf WHERE pf.id = (SELECT [PaymentFormId] FROM @changes)), 0)
					UPDATE @x SET value.modify(''delete /Contractor/PaymentFormDeadline'');
					UPDATE @x SET value.modify(''insert <PaymentFormDeadline>{sql:variable("@days")}</PaymentFormDeadline> as first into (/Contractor)[1]'');
				END
				
				if (SELECT [PayRegistryId] FROM @changes) <> 0
				BEGIN
					UPDATE @x SET value.modify(''delete /Contractor/PayRegistryId'');
					UPDATE @x SET value.modify(''insert <PayRegistryId>{sql:column("PayRegistryId")}</PayRegistryId> as first into (/Contractor)[1]'') FROM @changes;
				END
				
				if (SELECT [SaleKindId] FROM @changes) <> 0
				BEGIN
					UPDATE @x SET value.modify(''delete /Contractor/SaleKindId'');
					UPDATE @x SET value.modify(''insert <SaleKindId>{sql:column("SaleKindId")}</SaleKindId> as first into (/Contractor)[1]'') FROM @changes;
				END
				
				if (SELECT [PriceTypeId] FROM @changes) <> 0
				BEGIN
					UPDATE @x SET value.modify(''delete /Contractor/PriceTypeId'');
					UPDATE @x SET value.modify(''insert <PriceTypeId>{sql:column("PriceTypeId")}</PriceTypeId> as first into (/Contractor)[1]'') FROM @changes;
				END
				
				if (SELECT [AccountantParameter] FROM @changes) <> ''''
				BEGIN
					UPDATE @x SET value.modify(''delete /Contractor/AccountantParameter'');
					UPDATE @x SET value.modify(''insert <AccountantParameter>{sql:column("AccountantParameter")}</AccountantParameter> as first into (/Contractor)[1]'') FROM @changes;
				END
				
				if (SELECT [IsLimitActive] FROM @changes) <> ''false''
				BEGIN
					UPDATE @x SET value.modify(''delete /Contractor/IsLimitActive'');
					UPDATE @x SET value.modify(''insert <IsLimitActive>{sql:column("IsLimitActive")}</IsLimitActive> as first into (/Contractor)[1]'') FROM @changes;
				END
				
				if (SELECT [LimitCurrency] FROM @changes) <> ''''
				BEGIN
					UPDATE @x SET value.modify(''delete /Contractor/LimitCurrency'');
					UPDATE @x SET value.modify(''insert <LimitCurrency>{sql:column("LimitCurrency")}</LimitCurrency> as first into (/Contractor)[1]'') FROM @changes;
				END
				IF (SELECT [LimitValue] FROM @changes) <> 0
				BEGIN
					UPDATE @x SET value.modify(''delete /Contractor/LimitValue'');
					UPDATE @x SET value.modify(''insert <LimitValue>{sql:column("LimitValue")}</LimitValue> as first into (/Contractor)[1]'') FROM @changes;
				END
				
				IF (SELECT [PriceNegotiation] FROM @changes) <> ''false''
				BEGIN
					UPDATE @x SET value.modify(''delete /Contractor/PriceNegotiation'');
					UPDATE @x SET value.modify(''insert <PriceNegotiation>{sql:column("PriceNegotiation")}</PriceNegotiation> as first into (/Contractor)[1]'') FROM @changes;
				END
				
				IF (SELECT [DiscountPercent] FROM @changes) <> 0
				BEGIN
					UPDATE @x SET value.modify(''delete /Contractor/DiscountPercent'');
					UPDATE @x SET value.modify(''insert <DiscountPercent>{sql:column("DiscountPercent")}</DiscountPercent> as first into (/Contractor)[1]'') FROM @changes;
				END
				
				IF (SELECT [CatalogId] FROM @changes) <> 0
				BEGIN
					UPDATE @x SET value.modify(''delete /Contractor/CatalogId'');
					UPDATE @x SET value.modify(''insert <CatalogId>{sql:column("CatalogId")}</CatalogId> as first into (/Contractor)[1]'') FROM @changes;
				END
				
				IF (SELECT [ContractorKindId] FROM @changes) <> 0
				BEGIN
					UPDATE @x SET value.modify(''delete /Contractor/ContractorKindId'');
					UPDATE @x SET value.modify(''insert <ContractorKindId>{sql:column("ContractorKindId")}</ContractorKindId> as first into (/Contractor)[1]'') FROM @changes;
				END
				
				IF (SELECT [MarkerId] FROM @changes) <> 0
				BEGIN
					UPDATE @x SET value.modify(''delete /Contractor/MarkerId'');
					UPDATE @x SET value.modify(''insert <MarkerId>{sql:column("MarkerId")}</MarkerId> as first into (/Contractor)[1]'') FROM @changes;
				END
			END
			
			DECLARE @RightsKHElementID INT, @guidElement UNIQUEIDENTIFIER;
	
			SELECT @RightsKHElementID=ElementKindId FROM SSCommon.STDictionaries WHERE guid=''DEBA7687-E550-4CBC-9FE9-8175984F1AD4''

			SELECT @guidElement = elem.Guid FROM HM.ZZ zz
			INNER JOIN SSCommon.STElements elem ON elem.ElementId = zz.id2 
			WHERE typ = 204 AND id1 = @userid 

			UPDATE @x SET value.modify(''delete /Contractor/ContractorsGroupPermissions'');

			DECLARE @STUserRightsGroupsXML XML;

			SET @STUserRightsGroupsXML=(
			SELECT 
			CASE 
				WHEN elementy.Guid=@guidElement THEN ''true''
				ELSE ''false'' END AS HasRight ,Shortcut AS Name, ElementId
			from SSCommon.STUserRightsGroups urg 
			INNER JOIN SSCommon.STElements elementy ON elementy.ElementId=urg.ContractorRightGroup
			WHERE elementy.ElementKindId=@RightsKHElementID AND urg.UserId=@userid
			FOR XML PATH(''ContractorsGroupPermission''), ROOT(''ContractorsGroupPermissions'') )
			


			UPDATE @x SET value.modify(''insert (sql:variable("@STUserRightsGroupsXML")) as last into (/Contractor)[1]'')

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

	SET @metasql = 'USE ['+@dbName+']  EXEC (''' + REPLACE(@sql, '''', '''''') + ''')'
	
    EXEC (@metasql)

    FETCH NEXT FROM db_cursor INTO @dbName;
END;
CLOSE db_cursor;
DEALLOCATE db_cursor;