IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE [Name] = N'IDWew' AND Object_ID = Object_ID(N'SSCommon.STContractors'))
BEGIN
    ALTER TABLE [SSCommon].[STContractors] ADD [IDWew] NVARCHAR(5) NULL;
END
GO

IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE [Name] = N'IDWew' AND Object_ID = Object_ID(N'SSCommon.STContractorsRep'))
BEGIN
    ALTER TABLE [SSCommon].[STContractorsRep] ADD [IDWew] NVARCHAR(5) NULL;
END
GO

CREATE OR ALTER TRIGGER [SSCommon].[Contractors_TH_IOU] 
   ON [SSCommon].[STContractors] 
   INSTEAD OF UPDATE
AS 
BEGIN
	SET NOCOUNT ON;

    DECLARE @CI                varchar(128)     = context_info();
    DECLARE @st_modified_by    uniqueidentifier = '00000000-0000-0000-0000-000000000000';
    DECLARE @st_replication_id uniqueidentifier = '00000000-0000-0000-0000-000000000000';
    DECLARE @st_state          int              = 3;
    DECLARE @st_server         varchar(100)     = RTRIM(CONVERT(char(100), SERVERPROPERTY('servername')));
    DECLARE @st_last_modified  datetime         = GETDATE();
    DECLARE @is_mirror_db_guid char(1)          = '0'
    DECLARE @skip_history      char(1)          = '0';
    DECLARE @st_db_guid        uniqueidentifier = '00000000-0000-0000-0000-000000000000';    
    DECLARE @endpos            int              = CHARINDEX('*', @CI);
    
    if (@endpos > 0)
    BEGIN
	    DECLARE @divpos1 int = CHARINDEX('@', @CI);
	    DECLARE @divpos2 int = CHARINDEX('$', @CI);
	    DECLARE @divpos6 int = CHARINDEX('|', @CI);
	    DECLARE @divpos7 int = CHARINDEX('#', @CI);	    	    

        SET @st_modified_by    = SUBSTRING(@CI, 1,            @divpos1 - 1);
	    SET @st_replication_id = SUBSTRING(@CI, @divpos1 + 1, @divpos2 - @divpos1 - 1);
	    SET @is_mirror_db_guid = SUBSTRING(@CI, @divpos6 + 1, @divpos7 - @divpos6 - 1);
  	    SET @skip_history =		 SUBSTRING(@CI, @divpos7+1,   LEN(@CI) - @divpos7);

	    IF @is_mirror_db_guid = '1'
	    BEGIN			
			SET @st_db_guid = @st_replication_id;
			SET @st_replication_id = '00000000-0000-0000-0000-000000000000';
		END
    END;

    UPDATE SSCommon.[STContractors]
    SET 
    [Guid] = inserted1.[Guid], [Shortcut] = inserted1.[Shortcut], [Name] = inserted1.[Name], [NaturalPerson] = inserted1.[NaturalPerson], [NIP] = inserted1.[NIP], [PESEL] = inserted1.[PESEL], [REGON] = inserted1.[REGON], [NIP_UE] = inserted1.[NIP_UE], [Note] = inserted1.[Note], [ContactGuid] = inserted1.[ContactGuid], [BankingInfoGuid] = inserted1.[BankingInfoGuid], [LimitFlag] = inserted1.[LimitFlag], [LimitAmount] = inserted1.[LimitAmount], [LimitCurrency] = inserted1.[LimitCurrency], [VIES] = inserted1.[VIES], [Negotiation] = inserted1.[Negotiation], [VATPayerActive] = inserted1.[VATPayerActive], [LinkedUnit] = inserted1.[LinkedUnit], [MainElement] = inserted1.[MainElement], [MainPerson] = inserted1.[MainPerson], [VATGroup] = inserted1.[VATGroup], [JST] = inserted1.[JST], [IDWew] = inserted1.[IDWew],
    st_last_modified = @st_last_modified 
	 FROM SSCommon.[STContractors] as Contractors0 INNER JOIN inserted as inserted1 ON (inserted1.[Id]=Contractors0.[Id]) ;

	
	
 	IF(@skip_history <> '1')
	BEGIN
		INSERT INTO SSCommon.[STContractorsRep]
		([Id], [Guid], [Shortcut], [Name], [NaturalPerson], [NIP], [PESEL], [REGON], [NIP_UE], [Note], [ContactGuid], [BankingInfoGuid], [LimitFlag], [LimitAmount], [LimitCurrency], [VIES], [Negotiation], [VATPayerActive], [LinkedUnit], [MainElement], [MainPerson], [VATGroup], [JST], [IDWew], st_last_modified, st_shadowdata , st_state, st_modified_by, st_host, st_client, st_server, st_replication_id, st_db_guid)
		SELECT 
		    [Id], [Guid], [Shortcut], [Name], [NaturalPerson], [NIP], [PESEL], [REGON], [NIP_UE], [Note], [ContactGuid], [BankingInfoGuid], [LimitFlag], [LimitAmount], [LimitCurrency], [VIES], [Negotiation], [VATPayerActive], [LinkedUnit], [MainElement], [MainPerson], [VATGroup], [JST], [IDWew],
		    @st_last_modified,
		    NULL,
		    @st_state,
		    @st_modified_by,
		    HOST_NAME(),
		    APP_NAME(),
		    @st_server,
		    @st_replication_id, 
		    @st_db_guid
		FROM inserted;
	END
  

END;
GO

ALTER TABLE [SSCommon].[STContractors] ENABLE TRIGGER [Contractors_TH_IOU]
GO

CREATE OR ALTER TRIGGER [SSCommon].[Contractors_TH_IOI] 
   ON [SSCommon].[STContractors] 
   INSTEAD OF INSERT
AS 
BEGIN
	SET NOCOUNT ON;

    DECLARE @CI                varchar(128)     = context_info();
    DECLARE @st_modified_by    uniqueidentifier = '00000000-0000-0000-0000-000000000000';
    DECLARE @st_replication_id uniqueidentifier = '00000000-0000-0000-0000-000000000000';
    DECLARE @st_state          int              = 2;
    DECLARE @st_server         varchar(100)     = RTRIM(CONVERT(char(100), SERVERPROPERTY('servername')));
    DECLARE @st_last_modified  datetime         = GETDATE();
    DECLARE @ID_INS            char(1)          = '0';
    DECLARE @is_mirror_db_guid char(1)          = '0';
    DECLARE @skip_history      char(1)          = '0';
    DECLARE @st_db_guid        uniqueidentifier = '00000000-0000-0000-0000-000000000000';    
    DECLARE @endpos            int              = CHARINDEX('*', @CI);
    
    if (@endpos > 0)
    BEGIN
	    DECLARE @divpos1 int = CHARINDEX('@', @CI);
	    DECLARE @divpos2 int = CHARINDEX('$', @CI);
	    DECLARE @divpos4 int = CHARINDEX('^', @CI);
	    DECLARE @divpos5 int = CHARINDEX('&', @CI);
	    DECLARE @divpos6 int = CHARINDEX('|', @CI);
	    DECLARE @divpos7 int = CHARINDEX('#', @CI);	    	    

        SET @st_modified_by    = SUBSTRING(@CI, 1,            @divpos1 - 1);
	    SET @st_replication_id = SUBSTRING(@CI, @divpos1 + 1, @divpos2 - @divpos1 - 1);
	    SET @ID_INS            = SUBSTRING(@CI, @divpos4 + 1, @divpos5 - @divpos4 - 1);
	    SET @is_mirror_db_guid = SUBSTRING(@CI, @divpos6 + 1, @divpos7 - @divpos6 - 1);
   	    SET @skip_history =		 SUBSTRING(@CI, @divpos7+1,   LEN(@CI) - @divpos7);
	    
	    IF @is_mirror_db_guid = '1'
	    BEGIN			
			SET @st_db_guid = @st_replication_id;
			SET @st_replication_id = '00000000-0000-0000-0000-000000000000';
		END
    END;

 	IF(@skip_history <> '1')
	BEGIN
		INSERT INTO SSCommon.[STContractorsRep]
		([Id], [Guid], [Shortcut], [Name], [NaturalPerson], [NIP], [PESEL], [REGON], [NIP_UE], [Note], [ContactGuid], [BankingInfoGuid], [LimitFlag], [LimitAmount], [LimitCurrency], [VIES], [Negotiation], [VATPayerActive], [LinkedUnit], [MainElement], [MainPerson], [VATGroup], [JST], [IDWew], st_last_modified, st_shadowdata, st_state, st_modified_by, st_host, st_client, st_server, st_replication_id, st_db_guid)
		SELECT
			[Id], [Guid], [Shortcut], [Name], [NaturalPerson], [NIP], [PESEL], [REGON], [NIP_UE], [Note], [ContactGuid], [BankingInfoGuid], [LimitFlag], [LimitAmount], [LimitCurrency], [VIES], [Negotiation], [VATPayerActive], [LinkedUnit], [MainElement], [MainPerson], [VATGroup], [JST], [IDWew],
			@st_last_modified,
			NULL,
			@st_state,
			@st_modified_by,
			HOST_NAME(),
			APP_NAME(),
			@st_server,
			@st_replication_id, 
			@st_db_guid
		FROM inserted;
	END
	
	SET NOCOUNT OFF;
    
    IF @ID_INS = '1'   
		INSERT INTO SSCommon.[STContractors] 
		    ([Id], [Guid], [Shortcut], [Name], [NaturalPerson], [NIP], [PESEL], [REGON], [NIP_UE], [Note], [ContactGuid], [BankingInfoGuid], [LimitFlag], [LimitAmount], [LimitCurrency], [VIES], [Negotiation], [VATPayerActive], [LinkedUnit], [MainElement], [MainPerson], [VATGroup], [JST], [IDWew], st_last_modified, st_shadowdata) 
			SELECT [Id], [Guid], [Shortcut], [Name], [NaturalPerson], [NIP], [PESEL], [REGON], [NIP_UE], [Note], [ContactGuid], [BankingInfoGuid], [LimitFlag], [LimitAmount], [LimitCurrency], [VIES], [Negotiation], [VATPayerActive], [LinkedUnit], [MainElement], [MainPerson], [VATGroup], [JST], [IDWew], @st_last_modified, NULL
			FROM inserted;
	ELSE
		INSERT INTO SSCommon.[STContractors] 
		([Guid], [Shortcut], [Name], [NaturalPerson], [NIP], [PESEL], [REGON], [NIP_UE], [Note], [ContactGuid], [BankingInfoGuid], [LimitFlag], [LimitAmount], [LimitCurrency], [VIES], [Negotiation], [VATPayerActive], [LinkedUnit], [MainElement], [MainPerson], [VATGroup], [JST], [IDWew], st_last_modified, st_shadowdata)
			SELECT [Guid], [Shortcut], [Name], [NaturalPerson], [NIP], [PESEL], [REGON], [NIP_UE], [Note], [ContactGuid], [BankingInfoGuid], [LimitFlag], [LimitAmount], [LimitCurrency], [VIES], [Negotiation], [VATPayerActive], [LinkedUnit], [MainElement], [MainPerson], [VATGroup], [JST], [IDWew], @st_last_modified, NULL
			FROM inserted;

	SET NOCOUNT ON;

	

END;
GO

ALTER TABLE [SSCommon].[STContractors] ENABLE TRIGGER [Contractors_TH_IOI]
GO


CREATE OR ALTER TRIGGER [SSCommon].[Contractors_TH_AD] 
   ON [SSCommon].[STContractors] 
   AFTER DELETE
AS 
BEGIN
	SET NOCOUNT ON;

    DECLARE @CI                varchar(128)     = context_info();
    DECLARE @st_modified_by    uniqueidentifier = '00000000-0000-0000-0000-000000000000';
    DECLARE @st_replication_id uniqueidentifier = '00000000-0000-0000-0000-000000000000';
    DECLARE @st_state          int              = 1;
    DECLARE @st_server         varchar(100)     = RTRIM(CONVERT(char(100), SERVERPROPERTY('servername')));
    DECLARE @st_last_modified  datetime         = GETDATE();
    DECLARE @is_mirror_db_guid char(1)          = '0'
    DECLARE @skip_history      char(1)          = '0'       
    DECLARE @st_db_guid        uniqueidentifier = '00000000-0000-0000-0000-000000000000';    
    DECLARE @endpos            int               = CHARINDEX('*', @CI);
    
    if (@endpos > 0)
    BEGIN
	    DECLARE @divpos1 int = CHARINDEX('@', @CI);
	    DECLARE @divpos2 int = CHARINDEX('$', @CI);
	    DECLARE @divpos6 int = CHARINDEX('|', @CI);
	    DECLARE @divpos7 int = CHARINDEX('#', @CI);	    	    

        SET @st_modified_by    = SUBSTRING(@CI, 1,            @divpos1 - 1);
	    SET @st_replication_id = SUBSTRING(@CI, @divpos1 + 1, @divpos2 - @divpos1 - 1);
	    SET @is_mirror_db_guid = SUBSTRING(@CI, @divpos6 + 1, @divpos7 - @divpos6 - 1);
		SET @skip_history =		 SUBSTRING(@CI, @divpos7+1,   LEN(@CI) - @divpos7);

	    IF @is_mirror_db_guid = '1'
	    BEGIN			
			SET @st_db_guid = @st_replication_id;
			SET @st_replication_id = '00000000-0000-0000-0000-000000000000';
		END
    END;

	IF(@skip_history = '1')
		RETURN

	DECLARE @oldrecid int;
	DECLARE @Id int;
	DECLARE deletedrows CURSOR LOCAL FAST_FORWARD FOR  		
		SELECT DISTINCT MAX(c.st_seq_id) as st_seq_id, d.[Id] 
		FROM SSCommon.[STContractorsRep] c
		join deleted d ON c.Guid = d.Guid
		GROUP BY d.[Id] ;

	OPEN deletedrows;
	FETCH NEXT FROM deletedrows INTO @oldrecid, @Id;

	WHILE @@FETCH_STATUS = 0
	BEGIN
			
		INSERT INTO SSCommon.[STContractorsRep]
		(
			[Id], 
			[Guid], [Shortcut], [Name], [NaturalPerson], [NIP], [PESEL], [REGON], [NIP_UE], [Note], [ContactGuid], [BankingInfoGuid], [LimitFlag], [LimitAmount], [LimitCurrency], [VIES], [Negotiation], [VATPayerActive], [LinkedUnit], [MainElement], [MainPerson], [VATGroup], [JST], [IDWew],
			st_last_modified,
			st_shadowdata,
			st_state,
			st_modified_by,
			st_host,
			st_client,
			st_server,
			st_replication_id, 
			st_db_guid
		)
		SELECT 
			@Id,
			[Guid], [Shortcut], [Name], [NaturalPerson], [NIP], [PESEL], [REGON], [NIP_UE], [Note], [ContactGuid], [BankingInfoGuid], [LimitFlag], [LimitAmount], [LimitCurrency], [VIES], [Negotiation], [VATPayerActive], [LinkedUnit], [MainElement], [MainPerson], [VATGroup], [JST], [IDWew],
			st_last_modified,
			st_shadowdata,
			st_state,
			st_modified_by,
			st_host,
			st_client,
			st_server,
			st_replication_id, 
			st_db_guid
		FROM SSCommon.[STContractorsRep] WHERE st_seq_id = @oldrecid;

		DECLARE @newrecid int;
		SET @newrecid = SCOPE_IDENTITY();

		UPDATE SSCommon.[STContractorsRep]  
		SET
			st_last_modified = @st_last_modified,
			st_state = @st_state,
			st_modified_by = @st_modified_by,
			st_host = HOST_NAME(),
			st_client = APP_NAME(),
			st_server = @st_server,
			st_replication_id = @st_replication_id,
			st_db_guid = @st_db_guid
		WHERE st_seq_id = @newrecid;

		FETCH NEXT FROM deletedrows INTO @oldrecid, @Id;
	END;

	CLOSE deletedrows;
	DEALLOCATE deletedrows;    

END;
GO

ALTER TABLE [SSCommon].[STContractors] ENABLE TRIGGER [Contractors_TH_AD]
GO


-- Procedura umożliwiająca pobranie danych kontrahenta w postaci XML
CREATE OR ALTER PROCEDURE [Common].[ReadContractorAsXML] (@contractorId INT, @moduleCode NVARCHAR(255), @userid int)
AS
BEGIN
	BEGIN TRY
		DECLARE @RightsKHElementID INT;
		SELECT @RightsKHElementID=ElementKindId FROM SSCommon.STDictionaries WHERE guid='DEBA7687-E550-4CBC-9FE9-8175984F1AD4'

		DECLARE @classificationTableGuid UNIQUEIDENTIFIER
		DECLARE @classificationTableName NVARCHAR(255)

		SELECT @classificationTableName = ISNULL([TableNamePrefix], '') + [TableName], @classificationTableGuid = [Guid]
		FROM [SSCommon].[STClasifications] 
		WHERE [TypeTable] LIKE 'Contractors' AND [Owner] = @moduleCode

		IF @classificationTableName IS NULL -- jeżeli nie pobrano na podstawie kodu modułu to ustaw nazwę wspólnej tabeli kontrahentów
		BEGIN
			IF @moduleCode = 'FKF'
			BEGIN
				-- dla FKF ta sama klasyfikacja kontrahentów co dla HMF
				SELECT @classificationTableName = ISNULL([TableNamePrefix], '') + [TableName], @classificationTableGuid = [Guid]
				FROM [SSCommon].[STClasifications] 
				WHERE [TypeTable] LIKE 'Contractors' AND [Owner] = 'HMF'
			END
			ELSE
				SET @classificationTableName = 'STContractorsClassification'
		END

		CREATE TABLE #customFieldsDefinitions(
					[ColumnName] NVARCHAR(128) COLLATE database_default,
					[Symbol] NVARCHAR(128) COLLATE database_default,
					[Type] NVARCHAR(250) COLLATE database_default
					)

		INSERT INTO #customFieldsDefinitions
		SELECT
			[Common].[GetNormalizedColumnNameForCustomField](dim.[Symbol]),
			[Common].[GetNormalizedSymbolForCustomField](dim.[Symbol]),
			dim.[DimCLRType]
		from SSCommon.STClasifications clas
		INNER JOIN [SSCommon].[STClasificationDims] clasDim ON clasDim.[ClasificationsDefId] = clas.[ClasificationsDefId]
		INNER JOIN [SSCommon].[STDimensions] dim ON dim.[Id] = clasDim.[DimensionsId]
		where clas.[Guid] like @classificationTableGuid

		DECLARE @valueStatement NVARCHAR(MAX)
		CREATE TABLE #customFields(xmlData XML)
		IF EXISTS(SELECT * FROM #customFieldsDefinitions)
		BEGIN
			SET @valueStatement = (SELECT (CASE WHEN (cf.[Type] = 'System.Boolean, mscorlib')
				THEN (SELECT CONCAT('(CASE ', cf.[ColumnName],' WHEN 0 THEN ''false'' WHEN 1 THEN ''true'' ELSE NULL END) AS [' + cf.[Symbol] + '],'))
				ELSE (SELECT CONCAT('[', CAST(cf.[ColumnName] AS NVARCHAR(128)) + '] AS [' + cf.[Symbol] + '],'))
				END)
			FROM #customFieldsDefinitions cf
			FOR XML PATH(''), ELEMENTS XSINIL)
			SET @valueStatement = CONCAT('SELECT ', STUFF(@valueStatement, LEN(@valueStatement), 1, ''),' INTO #customFields FROM [SSCommon].[',@classificationTableName,'] WHERE [ElementId] = ',@contractorId,'; (SELECT * FROM #customFields) FOR XML PATH(''''), ELEMENTS XSINIL, TYPE')
			
			INSERT INTO #customFields
			EXECUTE (@valueStatement)
		END
		
		IF (OBJECT_ID('tempdb..#customFieldsDefinitions') IS NOT NULL) DROP TABLE #customFieldsDefinitions

		DECLARE @isUsedOnDocuments INT		
		exec @isUsedOnDocuments = HM.hm_Validate_KH_Delete @contractorId

		-- Prawo do wszystkich kontaktów
		DECLARE @cmf1000 bit = iif(EXISTS(SELECT TOP 1 Id
			FROM SSCommon.STEffPerm perm
			JOIN SSCommon.STEKOperations oper ON oper.OperationId = perm.OperId AND oper.Shortcut = 'CMF1000'
			WHERE UserId IS NOT NULL AND UserId = @userid), 1, 0);
		
        DECLARE @contractorNip NVARCHAR(20) = (
            SELECT [Common].ClearNonNumericCharacters([NIP]) 
            FROM [SSCommon].[STContractors] 
            WHERE [Id] = @contractorId);
		DECLARE @wlCheckStatus NVARCHAR(16);
		DECLARE @wlValidAt DATETIME2;
		SELECT TOP(1) @wlCheckStatus = [CheckStatus], @wlValidAt=Convert(date, [ValidAt]) 
			FROM [Common].[TaxPayersWLChecks] 
            WHERE [ContractorsNip] = @contractorNip OR [ContractorsNip] IS NULL AND [ContractorId] = @contractorId 
            ORDER BY [Id] DESC;

		DECLARE @resultXml XML = 
		(
			SELECT
				ctr.[Id] AS [Id],
				ctr.[Guid] AS [Guid],
				(CASE WHEN ctr.[NaturalPerson] = 1 THEN 'Osoba fizyczna (firma)' ELSE (CASE WHEN ctr.[NaturalPerson] = 2 THEN 'Osoba fizyczna' ELSE 'Firma' END) END) AS [Subject],
				ctr.[Name] AS [Name],
				ctr.[Shortcut] AS [Shortcut],
				IIF( @isUsedOnDocuments != 0,'true','false') AS [IsUsedOnDocuments],
				(
				SELECT
					--Dla firmy nie uzupełniamy imienia i nazwiska - będą one przy osobie kontaktowej
					CASE WHEN ctr.[MainPerson] IS NULL OR ctr.[NaturalPerson] = 0 THEN '' ELSE p.[FirstName] END AS [FirstName],
					CASE WHEN ctr.[MainPerson] IS NULL OR ctr.[NaturalPerson] = 0 THEN '' ELSE p.[Surname] END AS [SurName]
				FROM [SSCommon].[STContractors] ctr1
				LEFT OUTER JOIN [SSCommon].[STPersons] p ON ctr1.[MainPerson] = p.[Guid]
				WHERE ctr.[Id] = ctr1.[Id]
				FOR XML PATH(''), ELEMENTS XSINIL, TYPE
				),
				ctr.[NIP] AS [Nip],
				ctr.[NIP_UE] AS [NipUE],
				ctr.[PESEL] AS [Pesel],
				ctr.[REGON] AS [Regon],
                ISNULL(ctr.[IDWew], '') AS [IDWew],
				ctr.[Note] AS [Note],
				CASE WHEN ctr.[VIES] = 0 THEN 'false' ELSE 'true' END AS [Vies],
				CASE WHEN ctr.[VATPayerActive] = 0 THEN 'false' ELSE 'true' END  AS [VatPayerActive],
				ISNULL(@wlCheckStatus,'') AS [WLCheckStatus],
				ISNULL(CONVERT(VARCHAR(10), @wlValidAt),'') AS [WLValidAt],
				CASE WHEN ctr.[LinkedUnit] = 0 THEN 'false' ELSE 'true' END AS [LinkedUnit],
				CASE WHEN ctr.[VATGroup] = 0 THEN 'false' ELSE 'true' END AS [VatGroup],
				CASE WHEN ctr.[JST] = 0 THEN 'false' ELSE 'true' END AS [Jst],
				(
				SELECT
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE aMain.[Id] END AS [Id],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE CAST(aMain.[Guid] AS NVARCHAR(36)) END AS [Guid],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE aMain.[AddressName] END AS [Name],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE aMain.[PostOffice] END AS [Post],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE aMain.[PostCode] END AS [PostalCode],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE aMain.[Place] END AS [Place],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE aMain.[Street] END AS [Street],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE aMain.[HouseNo] END AS [HouseNo],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE aMain.[ApartmentNo] END AS [ApartmentNo],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE aMain.[Province] END AS [Province],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE aMain.[Country] END AS [Country]
				FOR XML PATH(''), ELEMENTS XSINIL, TYPE
				) AS [MainAddress],
				(
				SELECT
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE aCorsp.[Id] END AS [Id],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE CAST(aCorsp.[Guid] AS NVARCHAR(36)) END AS [Guid],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE aCorsp.[AddressName] END AS [Name],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE aCorsp.[PostOffice] END AS [Post],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE aCorsp.[PostCode] END AS [PostalCode],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE aCorsp.[Place] END AS [Place],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE aCorsp.[Street] END AS [Street],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE aCorsp.[HouseNo] END AS [HouseNo],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE aCorsp.[ApartmentNo] END AS [ApartmentNo],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE aCorsp.[Province] END AS [Province],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE aCorsp.[Country] END AS [Country]
				FOR XML PATH(''), ELEMENTS XSINIL, TYPE
				) AS [CorespondenceAddress],
				(
					ISNULL((SELECT
						acc.[Id] AS [Id],
						acc.[Guid] AS [Guid],
						acc.[AccountName] AS [AccountName],
						acc.[AccountNo] AS [AccountNo],
						acc.[BankName] AS [BankName],
						acc.[SWIFT_BIC] AS [SwiftBicNumber],
						CASE WHEN acc.[Active] = 1 THEN 'true' ELSE 'false' END as [IsActive],
						CASE WHEN acc.[Guid] = bi.[MainAccount] THEN 'true' ELSE 'false' END as [IsDefault],
						CASE WHEN acc.[ElementId] IS NULL THEN 'False' ELSE (SELECT [StringValue] FROM [SSCommon].[STElementExAttributes] WHERE [ElementId] = acc.[ElementId] AND [Name] LIKE 'Rachunek VAT') END AS [IsVatAccount],
						CASE WHEN acc.[ElementId] IS NULL THEN 'False' ELSE (SELECT [StringValue] FROM [SSCommon].[STElementExAttributes] WHERE [ElementId] = acc.[ElementId] AND [Name] LIKE 'Elektroniczny') END AS [IsElectronic],
						CASE WHEN acc.[WhiteList] IS NULL THEN '' ELSE (CASE WHEN acc.[WhiteList] = 1 THEN 'true' ELSE 'false' END) END as [IsInTaxPayerRegistry],
						CASE WHEN acc.[WhiteListDate] IS NULL THEN 'None' 
							ELSE (CASE WHEN acc.[WhiteList] = 1 THEN 
									(CASE WHEN DATEDIFF(dd, cast(acc.WhiteListDate as DATE), cast(GetDate() as date)) = 0 THEN 'Valid' ELSE 'ValidOverdue' END) 
								  ELSE 'Invalid' END) END as [IsInTaxPayerRegistryStatus],
						acc.[WhiteListDate] as [VerificationDate]
					FROM [SSCommon].[STBankingInfos] bi
					INNER JOIN [SSCommon].[STAccounts] acc ON acc.[BankingInfosGuid] = bi.[Guid]
					WHERE bi.[Guid] = ctr.[BankingInfoGuid]
					ORDER BY acc.[Id]
					FOR XML PATH('BankAccount'), ELEMENTS XSINIL, TYPE), '')
				) AS [BankAccounts],
				(
				SELECT
					CASE WHEN ctr.[MainPerson] IS NULL THEN '' ELSE p.[FirstName] END AS [FirstName],
					CASE WHEN ctr.[MainPerson] IS NULL THEN '' ELSE p.[Surname] END AS [SurName],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE t.[Facebook] END AS [Facebook],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE t.[Fax] END AS [Fax],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE t.[Telephone1] END AS [Telephone1],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE t.[Telephone2] END AS [Telephone2],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE t.[Telex] END AS [Tlf],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE t.[WWW] END AS [Website],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE t.[Email1] END AS [Email]
				FROM [SSCommon].[STContractors] ctr1
				LEFT OUTER JOIN [SSCommon].[STPersons] p ON ctr1.[MainPerson] = p.[Guid]
				LEFT OUTER JOIN [SSCommon].[STContacts] c ON ctr1.[ContactGuid] = c.[Guid]
				LEFT OUTER JOIN [SSCommon].[STTeleItContacts] t ON c.[MainTeleITContact] = t.[Guid]
				WHERE ctr.[Guid] = ctr1.[Guid] AND ctr.[Id] = @contractorId
				FOR XML PATH(''), ELEMENTS XSINIL, TYPE
				) AS [ContactPerson],
				CASE WHEN ctr.[LimitFlag] = 0 THEN 'false' ELSE 'true' END AS [IsLimitActive],
				ctr.[LimitCurrency] AS [LimitCurrency],
				CAST(ctr.[LimitAmount] AS MONEY) AS [LimitValue],
				CASE WHEN ctr.[Negotiation] = 0 THEN 'false' ELSE 'true' END AS [PriceNegotiation],
				(
					SELECT
						CASE WHEN elem.[Active] = 0 THEN 'Nieaktywny' ELSE 'Aktywny' END AS [Status], -- 0 - nieaktywny, 1 - aktywny
						ISNULL((SELECT CASE WHEN [StringValue] = 2 THEN 'Wymagaj' ELSE (CASE WHEN [StringValue] = 1 THEN 'Blokada' ELSE '' END) END FROM [SSCommon].[STElementExAttributes] ex WHERE ex.[ElementId] = elem.[ElementId] AND ex.[Name] LIKE 'splitPayment'), '') AS [RequestMppTransfers],
						(SELECT (CASE WHEN [StringValue] = '' THEN NULL ELSE [StringValue] END) FROM [SSCommon].[STElementExAttributes] ex WHERE ex.[ElementId] = elem.[ElementId] AND ex.[Name] LIKE 'idFK') AS [IdFk],
						CASE WHEN [Position] <= 0 THEN 0 ELSE [Position] END AS [PositionFk],
						ISNULL((SELECT [StringValue] FROM [SSCommon].[STElementExAttributes] ex WHERE ex.[ElementId] = elem.[ElementId] AND ex.[Name] LIKE 'parametrFK'), '') AS [AccountantParameter],
						ISNULL((SELECT [StringValue] FROM [SSCommon].[STElementExAttributes] ex WHERE ex.[ElementId] = elem.[ElementId] AND ex.[Name] LIKE 'typCeny'), '') AS [PriceTypeId],
						ISNULL((SELECT [StringValue] FROM [SSCommon].[STElementExAttributes] ex WHERE ex.[ElementId] = elem.[ElementId] AND ex.[Name] LIKE 'rodzajCeny'), '') AS [SaleKindId],
						ISNULL((SELECT [StringValue] FROM [SSCommon].[STElementExAttributes] ex WHERE ex.[ElementId] = elem.[ElementId] AND ex.[Name] LIKE 'rejestr'), '') AS [PayRegistryId],
						ISNULL((SELECT [StringValue] FROM [SSCommon].[STElementExAttributes] ex WHERE ex.[ElementId] = elem.[ElementId] AND ex.[Name] LIKE 'formaPlatnosci'), '') AS [PaymentFormId],
						ISNULL((SELECT [long] FROM [HM].[PaymentForm] pf WHERE pf.id = ISNULL((SELECT [StringValue] FROM [SSCommon].[STElementExAttributes] ex WHERE ex.[ElementId] = elem.[ElementId] AND ex.[Name] LIKE 'formaPlatnosci'), '')), 0) AS [PaymentFormDeadline]
					FROM [SSCommon].[STElements] elem
					WHERE ctr.[MainElement] = elem.[Guid] AND ctr.[Id] = @contractorId
					FOR XML PATH(''), ELEMENTS XSINIL, TYPE
				),
				(SELECT (ISNULL((SELECT elem.[ElementId] FROM [SSCommon].[STContractorsClassification] cmnCtrClas
					INNER JOIN [SSCommon].[STElements] elem ON elem.[Guid] = cmnCtrClas.[CDim_ZnacznikKh]
					WHERE cmnCtrClas.[ElementId] = ctr.[Guid]), ''))
					FOR XML PATH(''), ELEMENTS XSINIL, TYPE) as [MarkerId],
				(SELECT
					(SELECT ex.StringValue FROM [SSCommon].[STElementExAttributes] ex
					INNER JOIN [SSCommon].[STElements] elem ON elem.[ElementId] = ex.ElementId AND ex.[Name] = 'znacznik'
					WHERE elem.[Guid] = ctr.[MainElement] )
					FOR XML PATH(''), ELEMENTS XSINIL, TYPE) as [FkMarkerId],
				(SELECT (ISNULL((SELECT elem.[Shortcut] FROM [SSCommon].[STContractorsClassification] cmnCtrClas
					INNER JOIN [SSCommon].[STElements] elem ON elem.[Guid] = cmnCtrClas.[CDim_KatalogKh]
					WHERE cmnCtrClas.[ElementId] = ctr.[Guid]), ''))
					FOR XML PATH(''), ELEMENTS XSINIL, TYPE) as [CatalogId],
				(SELECT (ISNULL((SELECT elem.[ElementId] FROM [SSCommon].[STContractorsClassification] cmnCtrClas
					INNER JOIN [SSCommon].[STElements] elem ON elem.[Guid] = cmnCtrClas.[CDim_RodzajKh]
					WHERE cmnCtrClas.[ElementId] = ctr.[Guid]), ''))
					FOR XML PATH(''), ELEMENTS XSINIL, TYPE) as [ContractorKindId],
		-- dane handlowe
				(SELECT(CAST(ISNULL((SELECT [Discount] FROM [HM].[ContractorDiscounts] WHERE [ContractorId] = ctr.[Id]), 0)AS MONEY)) FOR XML PATH(''), ELEMENTS XSINIL, TYPE) AS [DiscountPercent],
				(SELECT
					-CAST([Receivables] AS MONEY) AS [Receivables],
					-CAST([Liabilities] AS MONEY) AS [Liabilities]
				FROM [HM].[ContractorLiabilitiesAndReceivables]
				WHERE [ContractorId] = ctr.[Id]
				FOR XML PATH(''), ELEMENTS XSINIL, TYPE),
				
		--role kontrahenta
				(
			
				SELECT 
				CASE isnull(grupy.ContractorId,0)
				WHEN 0 THEN 'false' 
				ELSE 'true' END AS HasRight,
				elementy.Shortcut AS Name, elementy.ElementId
				FROM  SSCommon.STUserRightsGroups urg 
				INNER JOIN SSCommon.STElements elementy ON elementy.ElementId=urg.ContractorRightGroup
				LEFT JOIN SSCommon.STContractorsRightsGroups grupy ON grupy.ContractorRightGroup = elementy.ElementId AND grupy.ContractorId=@contractorId
				WHERE elementy.ElementKindId=@RightsKHElementID AND urg.UserId=@userid
				FOR XML PATH('ContractorsGroupPermission'),ELEMENTS XSINIL, TYPE
				) AS [ContractorsGroupPermissions],
	
		-- kontakty
				ISNULL((
					SELECT cp.[Id], [FirstName], [SurName], [Role],
					ISNULL(cp_phone.[Content], '') AS [Phone],
					ISNULL(cp_email.[Content], '') AS [Email],
					[Tags],
					ISNULL(cp_email.[DocumentTypes], '') AS [DocumentTypes],
					[IsActive]
					FROM [Common].[ContactPersons] cp
					LEFT JOIN [Common].[ContactDetails] cp_phone ON (cp.[Id] = cp_phone.[ContactId] AND cp_phone.[IsDefault] = 1 AND cp_phone.[Type] IN (1, 2)) -- ConnectionType.BusinessPhone, ConnectionType.PrivatePhone
					LEFT JOIN [Common].[ContactDetails] cp_email ON (cp.[Id] = cp_email.[ContactId] AND cp_email.[IsDefault] = 1 AND cp_email.[Type] = 3) -- ConnectionType.Email
					WHERE [ContractorId] = ctr.[Id] AND @userid = IIF(@cmf1000 = 1, @userid, cp.[SupervisorId])
					FOR XML PATH('ContractorContactPerson'), ELEMENTS XSINIL, TYPE
				), '') AS [ContactPersonList],

		-- pola własne
				(SELECT * FROM #customFields)				
			FROM [SSCommon].[STContractors] ctr
			LEFT OUTER JOIN [SSCommon].[STContacts] c ON ctr.[ContactGuid] = c.[Guid]
			LEFT OUTER JOIN [SSCommon].[STPostOfficeAddresses] aMain ON c.[MainPostOfficeAddress] = aMain.[Guid]
			LEFT OUTER JOIN [SSCommon].[STPostOfficeAddresses] aCorsp ON c.[Guid] = aCorsp.[ContactGuid] AND aCorsp.[AddressName] LIKE 'adres korespondencyjny'
			WHERE ctr.[Id] = @contractorId
			FOR XML PATH(''), ROOT('Contractor')
		)
		IF (OBJECT_ID('tempdb..#customFields') IS NOT NULL) DROP TABLE #customFields

		EXEC [Common].[ReadContractorRIAsXML] @contractorId, @resultXml, @moduleCode
	END TRY
	BEGIN CATCH
		THROW;
	END CATCH
END

GO

-- Procedura umożliwiająca zapisanie danych kontrahenta z postaci XML
CREATE OR ALTER PROCEDURE [Common].[UpdateContractorFromXML] (@contractorData XML, @moduleCode NVARCHAR(255), @contextInfo VARCHAR(128), @contextInfoP VARCHAR(128), @contextInfoA VARCHAR(128), @userid int)
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.

	--Ustawienie CONTEXT_INFO z danymi potrzebnymi trigerom do zapisu historii zmian w tabelach z sufiksem 'Rep'
	declare @ContextInfoBinary as varbinary(128)
	SET @ContextInfoBinary = convert(varbinary(128), @contextInfo)
	SET CONTEXT_INFO @ContextInfoBinary;
	declare @ContextInfoBinaryP as varbinary(128)
	SET @ContextInfoBinaryP = convert(varbinary(128), @contextInfoP)
	declare @ContextInfoBinaryA as varbinary(128)
	SET @ContextInfoBinaryA = convert(varbinary(128), @contextInfoA)

	BEGIN TRY
		BEGIN TRANSACTION;
		
		-- tabela tymczasowa na dane kontrahenta z xml
		CREATE TABLE [#tblContractor]
		(
			[Id] INT,
			[Guid] UNIQUEIDENTIFIER,
			[Name] NVARCHAR(150) COLLATE database_default,
			[Shortcut] NVARCHAR(50) COLLATE database_default,
			[Nip] NVARCHAR(20) COLLATE database_default,
			[NipUE] NVARCHAR(20) COLLATE database_default,
			[Pesel] NVARCHAR(15) COLLATE database_default,
			[Regon] NVARCHAR(20) COLLATE database_default,
            [IDWew] NVARCHAR(5) COLLATE database_default,
			[LinkedUnit] BIT,
			[VatGroup] BIT,
			[Jst] BIT,
			[PersonKind] TINYINT,
			[VATPayerActive] BIT,
			[VIES] BIT,
				-- exAttribute
			[RequestMppTransfers] NVARCHAR(10) COLLATE database_default, -- splitPayment
			[PaymentFormId] INT, -- formaPlatnosci
			[PayRegistryId] INT, -- rejestr
			[SaleKindId] INT, -- rodzajCeny
			[PriceTypeId] INT, -- typCeny
			[AccountantParameter] NVARCHAR(40) COLLATE database_default, -- parametrFK
			[IdFk] NVARCHAR(1024), -- idFK
			[PositionFk] INT, -- pozycja FK
			-- additional data
			[CatalogId] NVARCHAR(100) COLLATE database_default,
			[ContractorKindId] INT,
			[MarkerId] INT,
			[FkMarkerId] INT,
			[Status] BIT,
			[Note] NVARCHAR(max) COLLATE database_default,
			-- sale conditions
			[IsLimitActive] BIT,
			[LimitCurrency] NVARCHAR(20) COLLATE database_default,
			[LimitValue] FLOAT,
			[PriceNegotiation] BIT,
			-- main person
			[FirstName] NVARCHAR(50) COLLATE database_default,
			[Surname] NVARCHAR(50) COLLATE database_default
		)
		-- tabela tymczasowa na dane kontaktowe kontrahenta z xml
		CREATE TABLE [#tblContact]
		(
			[Id] INT,
			[Guid] UNIQUEIDENTIFIER,
			[FirstName] NVARCHAR(50) COLLATE database_default,
			[Surname] NVARCHAR(50) COLLATE database_default,
			[Email] NVARCHAR(100) COLLATE database_default,
			[Facebook] NVARCHAR(100) COLLATE database_default,
			[Fax] NVARCHAR(50) COLLATE database_default,
			[Telephone1] NVARCHAR(50) COLLATE database_default,
			[Telephone2] NVARCHAR(50) COLLATE database_default,
			[Telex] NVARCHAR(50) COLLATE database_default,
			[Website] NVARCHAR(100) COLLATE database_default
		)
		-- tabela tymczasowa na dane adresu domyślnego kontrahenta z xml
		CREATE TABLE [#tblMainAddress]
		(
			[Id] INT,
			[Guid] UNIQUEIDENTIFIER,
			[Name] NVARCHAR(100) COLLATE database_default,
			[Post] NVARCHAR(50) COLLATE database_default,
			[PostalCode] NVARCHAR(10) COLLATE database_default,
			[Place] NVARCHAR(50) COLLATE database_default,
			[Street] NVARCHAR(50) COLLATE database_default,
			[HouseNo] NVARCHAR(15) COLLATE database_default,
			[ApartmentNo] NVARCHAR(15) COLLATE database_default,
			[Province] NVARCHAR(50) COLLATE database_default,
			[Country] NVARCHAR(50) COLLATE database_default
		)
		-- tabela tymczasowa na dane adresu korespondencyjnego kontrahenta z xml
		CREATE TABLE [#tblCorespondenceAddress]
		(
			[Id] INT,
			[Guid] UNIQUEIDENTIFIER,
			[Name] NVARCHAR(100) COLLATE database_default,
			[Post] NVARCHAR(50) COLLATE database_default,
			[PostalCode] NVARCHAR(10) COLLATE database_default,
			[Place] NVARCHAR(50) COLLATE database_default,
			[Street] NVARCHAR(50) COLLATE database_default,
			[HouseNo] NVARCHAR(15) COLLATE database_default,
			[ApartmentNo] NVARCHAR(15) COLLATE database_default,
			[Province] NVARCHAR(50) COLLATE database_default,
			[Country] NVARCHAR(50) COLLATE database_default
		)
		-- tabela tymczasowa na dane handlowe kontrahenta z xml
		CREATE TABLE [#tblTradeData]
		(
			[DiscountPercent] FLOAT,
			[Receivables] FLOAT,
			[Liabilities] FLOAT
		)
		-- tabela tymczasowa na pola z rachunkami bankowymi
		CREATE TABLE [#tblBankAccounts]
		(
			[Id] INT,
			[Guid] UNIQUEIDENTIFIER,
			[AccountNo] NVARCHAR(50) COLLATE database_default,
			[AccountName] NVARCHAR(100) COLLATE database_default,
			[IsDefault] BIT,
			[IsActive] BIT,
			[WhiteList] BIT,
			[WhiteListDate] DATETIME2,
			[SWIFT_BIC] VARCHAR(11) COLLATE database_default,
			[BankName] NVARCHAR(100) COLLATE database_default,
			-- extended attributes
			[IsElectronic] BIT,
			[IsVatAccount] BIT
		)
		-- tabela tymczasowa na pola z rachunkami bankowymi
		CREATE TABLE [#tblCustomFields]
		(
			[FieldName] NVARCHAR(100) COLLATE database_default,
			[Value] NVARCHAR(MAX) COLLATE database_default,
			[ColumnName] NVARCHAR(128) COLLATE database_default,
			[Type] NVARCHAR(250) COLLATE database_default
		)
		
		-- tabela tymczasowa na ElementExAttributes
		CREATE TABLE [#ExtAttributes]
		(
			[ElementId] INT,
			[Name] NVARCHAR(50) COLLATE database_default,
			[StringValue] NVARCHAR(1024) COLLATE database_default,
			[OriginalCLRType] NVARCHAR(400) COLLATE database_default,
			[ElementExAttrId] INT IDENTITY(1,1),
			[Guid] UNIQUEIDENTIFIER,
			[Title] NVARCHAR(200) COLLATE database_default,
			[AccessFlag] BIT,
			[InheritedFlag] BIT
		)
		
		--tabela tymczasowa na grupy kontrahentów
		CREATE TABLE [#ContractorsGroupPermissions]
		(
			[HasRight] BIT,
			[ElementId] INT,
			[Name] NVARCHAR(100) COLLATE database_default
		)

		DECLARE @extAttElementId int
		DECLARE @extAttTitle nvarchar (200)
		DECLARE @extAttActive bit
		DECLARE @extAttAccountNo nvarchar (40)
		DECLARE @extAttDescription nvarchar (4000)
		DECLARE @extAttShortcut nvarchar (100)
		DECLARE @extAttPosition int
		DECLARE @extAttExtAttributes nvarchar(max)
		DECLARE @extAttExtObjVer int
		DECLARE @extAttExtWriteAction int
		DECLARE @extAttExtOtherThenOriginal smallint

		-- przygotowanie xml
		DECLARE @handle INT, @prepareXmlStatus INT
		EXEC @prepareXmlStatus = sp_xml_preparedocument @handle OUTPUT, @contractorData

		-- deserializacja danych kontaktowych z xml
		INSERT INTO [#tblContact]
		SELECT 
			ISNULL([Id], 0), CASE WHEN [Guid] IS NULL OR [Guid] = '' OR [Guid] = '00000000-0000-0000-0000-000000000000' THEN NEWID() ELSE [GUID] END,
			[FirstName], [SurName], [Email], [Facebook], [Fax], [Telephone1], [Telephone2], [Telex], [Website]
		FROM 
			OPENXML(@handle, '/Contractor/ContactPerson')
			WITH
			(
				[Id] INT 'Id',
				[Guid] NVARCHAR(36) 'Guid',
				[FirstName] NVARCHAR(50) 'FirstName',
				[Surname] NVARCHAR(50) 'SurName',
				[Email] NVARCHAR(100) 'Email',
				[Facebook] NVARCHAR(100) 'Facebook',
				[Fax] NVARCHAR(50) 'Fax',
				[Telephone1] NVARCHAR(50) 'Telephone1',
				[Telephone2] NVARCHAR(50) 'Telephone2',
				[Telex] NVARCHAR(50) 'Tlf',
				[Website] NVARCHAR(100) 'Website'
			)
		-- wyczyszczenie pustych elementów
		DELETE FROM [#tblContact]
		WHERE [FirstName] = '' AND [Surname] = '' AND [Email] = '' AND [Facebook] = '' AND [Fax] = '' AND [Telephone1] = '' AND [Telephone2] = '' AND [Telex] = '' AND [Website] = ''
			AND [Guid] = '00000000-0000-0000-0000-000000000000' --dla nowych kontaktów, dla istniejących chcemy wyczyścić pola w bazie
		--select * from #tblContact

		-- deserializacja danych adresu domyślnego z xml
		INSERT INTO [#tblMainAddress]
		SELECT 
			ISNULL([Id], 0),
			CASE WHEN [Guid] IS NULL OR [Guid] = '' THEN '00000000-0000-0000-0000-000000000000' ELSE [GUID] END,
			[Name], [Post], [PostalCode], [Place], [Street], [HouseNo], [ApartmentNo], [Province], [Country]
		FROM
			OPENXML(@handle, '/Contractor/MainAddress')
			WITH
			(
				[Id] INT 'Id',
				[Guid] NVARCHAR(36) 'Guid',
				[Name] NVARCHAR(100) 'Name',
				[Post] NVARCHAR(50) 'Post',
				[PostalCode] NVARCHAR(10) 'PostalCode',
				[Place] NVARCHAR(50) 'Place',
				[Street] NVARCHAR(50) 'Street',
				[HouseNo] NVARCHAR(15) 'HouseNo',
				[ApartmentNo] NVARCHAR(15) 'ApartmentNo',
				[Province] NVARCHAR(50) 'Province',
				[Country] NVARCHAR(50) 'Country'
			)
		-- wyczyszczenie pustych elementów
		DELETE FROM [#tblMainAddress]
		WHERE [Name] = '' AND [Post] = '' AND [PostalCode] = '' AND [Place] = '' AND [Street] = '' AND [HouseNo] = '' AND [ApartmentNo] = '' AND [Province] = '' AND [Country] = ''
		--select * from [#tblMainAddress]
		
		-- deserializacja danych adresu korespondencyjnego z xml
		INSERT INTO [#tblCorespondenceAddress]
		SELECT 
			ISNULL([Id], 0), CASE WHEN [Guid] IS NULL OR [Guid] = '' THEN '00000000-0000-0000-0000-000000000000' ELSE [GUID] END,
			[Name], [Post], [PostalCode], [Place], [Street], [HouseNo], [ApartmentNo], [Province], [Country]
		FROM 
			OPENXML(@handle, '/Contractor/CorespondenceAddress')
			WITH
			(
				[Id] INT 'Id',
				[Guid] NVARCHAR(36) 'Guid',
				[Name] NVARCHAR(100) 'Name',
				[Post] NVARCHAR(50) 'Post',
				[PostalCode] NVARCHAR(10) 'PostalCode',
				[Place] NVARCHAR(50) 'Place',
				[Street] NVARCHAR(50) 'Street',
				[HouseNo] NVARCHAR(15) 'HouseNo',
				[ApartmentNo] NVARCHAR(15) 'ApartmentNo',
				[Province] NVARCHAR(50) 'Province',
				[Country] NVARCHAR(50) 'Country'
			)
		-- wyczyszczenie pustych elementów
		DELETE FROM [#tblCorespondenceAddress]
		WHERE [Name] = '' AND [Post] = '' AND [PostalCode] = '' AND [Place] = '' AND [Street] = '' AND [HouseNo] = '' AND [ApartmentNo] = '' AND [Province] = '' AND [Country] = ''
		--select * from [#tblCorespondenceAddress]
		
		-- deserializacja danych handlowych z xml
		INSERT INTO [#tblTradeData]
		SELECT 
			[DiscountPercent], [Receivables], [Liabilities]
		FROM 
			OPENXML(@handle, '/Contractor')
			WITH
			(
				[DiscountPercent] FLOAT 'DiscountPercent',
				[Receivables] FLOAT 'Receivables',
				[Liabilities] FLOAT 'Liabilities'
			)
		DELETE FROM [#tblTradeData]
		WHERE [DiscountPercent] = 0 AND [Receivables] = 0 AND [Liabilities] = 0
		--select * from [#tblTradeData]
		
		-- deserializacja danych rachunków bankowych z xml
		INSERT INTO [#tblBankAccounts]
		SELECT 
			[Id],CASE WHEN [Guid] IS NULL OR [Guid] = '' OR [Guid] = '00000000-0000-0000-0000-000000000000' THEN NEWID() ELSE [GUID] END,
			[AccountNo], [AccountName], [IsDefault], [IsActive],
			CASE WHEN [WhiteList] IS NULL OR [WhiteList] = '' THEN NULL ELSE (CASE WHEN [WhiteList] = 'true' THEN 1 ELSE 0 END) END [WhiteList],
			CASE WHEN [WhiteListDate] IS NULL OR [WhiteListDate] = '' THEN NULL ELSE cast([WhiteListDate] as DATETIME2) END [WhiteListDate],
			[SWIFT_BIC], [BankName],
			-- extended attributes
			[IsElectronic], [IsVatAccount]
		FROM 
			OPENXML(@handle, '/Contractor/BankAccounts/BankAccount')
			WITH
			(
				[Id] INT 'Id',
				[Guid] NVARCHAR(36) 'Guid',
				[AccountNo] NVARCHAR(50) 'AccountNo',
				[AccountName] NVARCHAR(100) 'AccountName',
				[IsDefault] BIT 'IsDefault',
				[IsActive] BIT 'IsActive',
				[WhiteList] NVARCHAR(5) 'IsInTaxPayerRegistry',
				[WhiteListDate] NVARCHAR(40) 'VerificationDate',
				[SWIFT_BIC] VARCHAR(11) 'SwiftBicNumber',
				[BankName] NVARCHAR(100) 'BankName',
				-- extended attributes
				[IsElectronic] BIT 'IsElectronic',
				[IsVatAccount] BIT 'IsVatAccount'
			)
			
		DELETE FROM [#tblBankAccounts]
		WHERE ([AccountNo] IS NULL OR [AccountNo] = '') AND ([AccountName] IS NULL OR [AccountName] = '') AND ([IsDefault] IS NULL OR [IsDefault] = 0) AND ([IsActive] IS NULL OR [IsActive] = 0)
			AND [WhiteList] = NULL AND [WhiteListDate] = NULL AND ([SWIFT_BIC] IS NULL OR [SWIFT_BIC] = '') AND ([BankName] IS NULL OR [BankName] = '')
			AND ([IsElectronic] IS NULL OR [IsElectronic] = 0)AND ([IsVatAccount] IS NULL OR [IsVatAccount] = 0)
		--select * from [#tblBankAccounts]

		-- deserializacja danych kontrahenta z xml
		INSERT INTO [#tblContractor]
		SELECT 
			[Id], CASE WHEN [Guid] IS NULL OR [Guid] = '' OR [Guid] = '00000000-0000-0000-0000-000000000000' THEN NEWID() ELSE [GUID] END,
			[Name], [Shortcut], [Nip], [NipUE], [Pesel], [Regon], [IDWew], [LinkedUnit], [VatGroup], [Jst],
			(CASE WHEN [PersonKind] LIKE 'Osoba fizyczna (firma)' THEN 1 ELSE (CASE WHEN [PersonKind] = 'Osoba fizyczna' THEN 2 ELSE 0 END) END) AS [Subject],
			[VATPayerActive], [VIES],
			-- exAttribute
			[RequestMppTransfers], [PaymentFormId], [PayRegistryId], [SaleKindId], [PriceTypeId], [AccountantParameter], [IdFk], [PositionFk],
			-- additional data
			[CatalogId], [ContractorKindId], [MarkerId], [FkMarkerId], (CASE WHEN [Status] IS NULL OR [Status] LIKE '' OR [Status] LIKE 'Aktywny' THEN 1 ELSE 0 END) AS [Status], [Note],
			-- sale conditions
			[IsLimitActive], [LimitCurrency], [LimitValue], [PriceNegotiation],
			-- main person
			[FirstName], [Surname]
		FROM 
			OPENXML(@handle, '/Contractor')
			WITH
			(
				[Id] INT 'Id',
				[Guid] NVARCHAR(36) 'Guid',
				[Name] NVARCHAR(150) 'Name',
				[Shortcut] NVARCHAR(50) 'Shortcut',
				[Nip] NVARCHAR(20) 'Nip',
				[NipUE] NVARCHAR(20) 'NipUE',
				[Pesel] NVARCHAR(15) 'Pesel',
				[Regon] NVARCHAR(20) 'Regon',
                [IDWew] NVARCHAR(5) 'IDWew',
				[LinkedUnit] BIT 'LinkedUnit',
				[VatGroup] BIT 'VatGroup',
				[Jst] BIT 'Jst',
				[PersonKind] NVARCHAR(100) 'Subject',
				[VATPayerActive] BIT 'VatPayerActive',
				[VIES] BIT 'Vies',
				-- exAttribute
				[RequestMppTransfers] NVARCHAR(10) 'RequestMppTransfers', -- splitPayment
				[PaymentFormId] INT 'PaymentFormId', -- formaPlatnosci
				[PayRegistryId] INT 'PayRegistryId', -- rejestr
				[SaleKindId] INT 'SaleKindId', -- rodzajCeny
				[PriceTypeId] INT 'PriceTypeId', -- typCeny
				[AccountantParameter] NVARCHAR(40) 'AccountantParameter', -- parametrFK
				[IdFk] NVARCHAR(1024) 'IdFk', -- idFK
				[PositionFk] INT 'PositionFk', -- pozycja FK
				-- additional data
				[CatalogId] NVARCHAR(100) 'CatalogId',
				[ContractorKindId] INT 'ContractorKindId',
				[MarkerId] INT 'MarkerId',
				[FKMarkerId] INT 'FkMarkerId',
				[Status] NVARCHAR(10) 'Status',
				[Note] NVARCHAR(max) 'Note',
				-- sale conditions
				[IsLimitActive] BIT 'IsLimitActive',
				[LimitCurrency] NVARCHAR(20) 'LimitCurrency',
				[LimitValue] FLOAT 'LimitValue',
				[PriceNegotiation] BIT 'PriceNegotiation',
				-- main person
				[FirstName] NVARCHAR(50) 'FirstName',
				[Surname] NVARCHAR(50) 'SurName'
			)
		--select * from [#tblContractor]

		-- ustawienie domyślnego znacznika; wpp. bug 195961
		UPDATE [#tblContractor]
		   SET [MarkerId] = (SELECT [ElementId] FROM [SSCommon].[STElements] WHERE [Guid] = 'D205CFCC-1AF4-4E6B-919C-9AE3A2192F81')
		 WHERE 0 = ISNULL([MarkerId], 0);

		-- deserializacja pól własnych kontrahenta z xml
		DECLARE @classificationTableGuid UNIQUEIDENTIFIER
		DECLARE @classificationTableName NVARCHAR(255)

		SELECT @classificationTableName = ISNULL([TableNamePrefix], '') + [TableName], @classificationTableGuid = [Guid]
		FROM [SSCommon].[STClasifications] 
		WHERE [TypeTable] LIKE 'Contractors' AND [Owner] = @moduleCode

		IF @classificationTableName IS NULL -- jeżeli nie pobrano na podstawie kodu modułu to ustaw nazwę wspólnej tabeli kontrahentów
		BEGIN
			IF @moduleCode = 'FKF'
			BEGIN
				-- dla FKF ta sama klasyfikacja kontrahentów co dla HMF
				SELECT @classificationTableName = ISNULL([TableNamePrefix], '') + [TableName], @classificationTableGuid = [Guid]
				FROM [SSCommon].[STClasifications] 
				WHERE [TypeTable] LIKE 'Contractors' AND [Owner] = 'HMF'
			END
			ELSE
				SET @classificationTableName = 'STContractorsClassification'
		END

		DECLARE @customFieldsDefinitions TABLE([ColumnName] NVARCHAR(128) COLLATE database_default,[Symbol] NVARCHAR(128) COLLATE database_default,[Type] NVARCHAR(250) COLLATE database_default)
		INSERT INTO @customFieldsDefinitions
		SELECT
			[Common].[GetNormalizedColumnNameForCustomField](dim.[Symbol]),
			[Common].[GetNormalizedSymbolForCustomField](dim.[Symbol]),
			dim.[DimCLRType]
		from [SSCommon].[STClasifications] clas
		INNER JOIN [SSCommon].[STClasificationDims] clasDim ON clasDim.[ClasificationsDefId] = clas.[ClasificationsDefId]
		INNER JOIN [SSCommon].[STDimensions] dim ON dim.[Id] = clasDim.[DimensionsId]
		where clas.[Guid] like @classificationTableGuid
		--SELECT * FROM @customFieldsDefinitions

		-- czy są zdefiniowane pola własne
		IF EXISTS (SELECT * FROM @customFieldsDefinitions)
		BEGIN
			SELECT * INTO #temporaryContractorData
			FROM OPENXML(@handle, '/Contractor')
			;WITH documentFields as (
				SELECT c0.[parentid] AS [Id], c0.[localname] AS [FieldName], c1.[text] AS [Value]
				FROM #temporaryContractorData c0
				INNER JOIN #temporaryContractorData c1 ON c0.[id] = c1.[parentid]
				WHERE c0.[parentid] = 0 AND c0.[nodetype] = 1 AND c1.[nodetype] = 3 -- nodetype	int	Identifies the node type. 1 = Element node, 2 = Attribute node, 3 = Text node
			)
			INSERT INTO [#tblCustomFields]
			SELECT customFieldDefinitions.[Symbol] AS [FieldName], documentFields.[Value] AS [Value], customFieldDefinitions.[ColumnName] AS [ColumnName], customFieldDefinitions.[Type] AS [Type]
			FROM documentFields
			RIGHT OUTER JOIN @customFieldsDefinitions customFieldDefinitions ON customFieldDefinitions.[Symbol] = documentFields.[FieldName]

			IF (OBJECT_ID('tempdb..#temporaryContractorData') IS NOT NULL) DROP TABLE #temporaryContractorData
			--SELECT * FROM [#tblCustomFields]
		END

		DECLARE @contractorId INT, @contractorGuid UNIQUEIDENTIFIER
		SELECT @contractorId = [Id], @contractorGuid = CASE WHEN [Guid] IS NULL OR [Guid] LIKE '00000000-0000-0000-0000-000000000000' THEN NEWID() ELSE [Guid] END FROM [#tblContractor]
		DECLARE @contactId INT
		DECLARE @contactGuid UNIQUEIDENTIFIER
		SET @contactGuid = NULL
		DECLARE @bankingInfoGuid UNIQUEIDENTIFIER
		SET @bankingInfoGuid = NULL
		IF ((@contactId IS NULL OR @contactId = 0) AND @contractorId IS NOT NULL AND @contractorId <> 0)
		BEGIN
			SELECT
				@contactGuid = ctr.[ContactGuid],
				@contactId = ct.[Id],
				@bankingInfoGuid = [BankingInfoGuid]
			FROM [SSCommon].[STContractors] ctr
			INNER JOIN [SSCommon].[STContacts] ct ON ct.[Guid] = ctr.[ContactGuid]
			WHERE ctr.[Id] = @contractorId
		END

-- zapis informacji do tabeli z danymi kontaktowymi
		-- tabela tymczasowa na TeleItContacts
		CREATE TABLE [#TeleItContacts]
		(
			[Id] INT IDENTITY(1,1),
			[Guid] UNIQUEIDENTIFIER,
			[Name] NVARCHAR(50) COLLATE database_default,
			[ContactGuid] UNIQUEIDENTIFIER,
			[Telephone1] NVARCHAR(50) COLLATE database_default,
			[Telephone2] NVARCHAR(50) COLLATE database_default,
			[Telephone3] NVARCHAR(50) COLLATE database_default,
			[Fax] NVARCHAR(50) COLLATE database_default,
			[Fax2] NVARCHAR(50) COLLATE database_default,
			[Telex] NVARCHAR(50) COLLATE database_default,
			[Email1] NVARCHAR(100) COLLATE database_default,
			[Email2] NVARCHAR(100) COLLATE database_default,
			[WWW] NVARCHAR(100) COLLATE database_default,
			[Communicator1] NVARCHAR(50) COLLATE database_default,
			[Communicator2] NVARCHAR(50) COLLATE database_default,
			[Description] NVARCHAR(3000) COLLATE database_default,
			[Facebook] NVARCHAR(100) COLLATE database_default
		)

		-- tabela tymczasowa na PostOfficeAddresses
		CREATE TABLE [#PostOfficeAddresses]
		(
			[Id] INT IDENTITY(1,1),
			[AddressName] NVARCHAR(64) COLLATE database_default,
			[Guid] UNIQUEIDENTIFIER,
			[Street] NVARCHAR(50) COLLATE database_default,
			[Commune] NVARCHAR(50) COLLATE database_default,
			[District] NVARCHAR(50) COLLATE database_default,
			[Place] NVARCHAR(50) COLLATE database_default,
			[PostCode] NVARCHAR(10) COLLATE database_default,
			[PostOffice] NVARCHAR(50) COLLATE database_default,
			[ApartmentNo] NVARCHAR(15) COLLATE database_default,
			[HouseNo] NVARCHAR(15) COLLATE database_default,
			[ContactGuid] UNIQUEIDENTIFIER,
			[Province] NVARCHAR(50) COLLATE database_default,
			[Country] NVARCHAR(50) COLLATE database_default,
			[PostOfficeBox] NVARCHAR(50) COLLATE database_default
		)
		
		DECLARE @contactName NVARCHAR(100) = ''
		DECLARE @contactMainPostOfficeAddress UNIQUEIDENTIFIER
		DECLARE @contactMainTeleITContact UNIQUEIDENTIFIER
		DECLARE @contactPostOfficeAddresses NVARCHAR(max)
		DECLARE @contactTeleItContacts NVARCHAR(max)
		DECLARE @contactWriteAction INT = 0

		IF EXISTS (SELECT * FROM [#tblContact])
		BEGIN
			
			IF (@contactId IS NULL OR @contactId = 0) -- nowy kontakt
			BEGIN
				IF (@contactGuid IS NULL OR @contactGuid LIKE '00000000-0000-0000-0000-000000000000')
					SELECT @contactGuid = CASE WHEN [Guid] IS NOT NULL AND [Guid] NOT LIKE '00000000-0000-0000-0000-000000000000' THEN [Guid] ELSE NEWID() END FROM [#tblContact]

				SELECT 
					@contactName = '',
					@contactMainTeleITContact = NEWID(),
					@contactWriteAction = 1
			END
			ELSE -- istniejący kontakt
			BEGIN
				SELECT 
					@contactName = [Name], 
					@contactWriteAction = 2
				FROM [SSCommon].[STContacts] 
				WHERE [Guid] = @contactGuid

				SELECT @contactMainTeleITContact = [Guid] FROM [SSCommon].[STTeleItContacts] WHERE [ContactGuid] LIKE @contactGuid
				IF (@contactMainTeleITContact IS NULL)
					SET @contactMainTeleITContact = NEWID()
			END
			INSERT INTO [#TeleItContacts]([Guid], [Name], [ContactGuid], [Telephone1], [Telephone2], [Telephone3], [Fax], [Fax2], [Email1], [Email2], [WWW],
				[Communicator1], [Communicator2], [Description], [Telex], [Facebook])
			SELECT @contactMainTeleITContact, 'domyślny adres IT', @contactGuid, ISNULL(c.[Telephone1], ''), ISNULL(c.[Telephone2], ''), '', ISNULL(c.[Fax], ''), '', ISNULL(c.[Email], ''), '', ISNULL(c.[Website], ''),
				'', '', '', ISNULL(c.[Telex], ''), ISNULL(c.[Facebook], '')
			FROM [#tblContact] c
		END

		-- zapis informacji do tabeli z danymi adresu domyślnego
		IF EXISTS (SELECT * FROM [#tblMainAddress])
		BEGIN
			IF (@contactGuid IS NULL)
				SET @contactGuid = NEWID()

			DECLARE @postOfficeAddressName NVARCHAR(64)
			SELECT @contactMainPostOfficeAddress = [Guid], @postOfficeAddressName = [Name] FROM [#tblMainAddress]

			IF (@contactId IS NULL OR @contactId = 0) -- nowy kontakt
			BEGIN
				SELECT @contactId = [Id] FROM [SSCommon].[STContacts] WHERE [Guid] LIKE @contactGuid
				IF (@contactMainPostOfficeAddress IS NULL OR @contactMainPostOfficeAddress LIKE '00000000-0000-0000-0000-000000000000')
					SET @contactMainPostOfficeAddress = NEWID()
			END
			ELSE -- istniejący kontakt
			BEGIN
				IF EXISTS(SELECT * FROM [SSCommon].[STPostOfficeAddresses] WHERE [ContactGuid] LIKE @contactGuid AND [AddressName] LIKE @postOfficeAddressName)
					SELECT @contactMainPostOfficeAddress = [Guid] FROM [SSCommon].[STPostOfficeAddresses] WHERE [ContactGuid] LIKE @contactGuid AND [AddressName] LIKE @postOfficeAddressName
				ELSE
					IF (@contactMainPostOfficeAddress IS NULL OR @contactMainPostOfficeAddress LIKE '00000000-0000-0000-0000-000000000000')
						SET @contactMainPostOfficeAddress = NEWID()
			END
			INSERT INTO [#PostOfficeAddresses]([AddressName], [Guid], [Street], [Commune], [District], [Place], [PostCode], [PostOffice], [ApartmentNo],
				[HouseNo], [ContactGuid], [Province], [Country], [PostOfficeBox])
			SELECT CASE WHEN a.[Name] IS NULL OR a.[Name] = '' THEN 'adres domyślny' ELSE a.[Name] END, @contactMainPostOfficeAddress, a.[Street], '', '', a.[Place], a.[PostalCode], a.[Post], a.[ApartmentNo],
				a.[HouseNo], @contactGuid, a.[Province], a.[Country], ''
			FROM [#tblMainAddress] a
		END	

		-- zapis informacji do tabeli z danymi adresu korespondencyjnego
		IF EXISTS (SELECT * FROM [#tblCorespondenceAddress])
		BEGIN
			DECLARE @corespondenceAddressId INT
			DECLARE @corespondenceAddressGuid UNIQUEIDENTIFIER
			DECLARE @corespondenceAddressName NVARCHAR(64)
			SELECT  @corespondenceAddressId = [Id], @corespondenceAddressGuid = [Guid], @corespondenceAddressName = [Name] FROM [#tblCorespondenceAddress]

			IF (@corespondenceAddressId IS NULL OR @corespondenceAddressId = 0) -- nowy adres korespondencyjny
				IF (@corespondenceAddressGuid IS NULL OR @corespondenceAddressGuid LIKE '00000000-0000-0000-0000-000000000000')
					SET @corespondenceAddressGuid = NEWID()	
			ELSE
			BEGIN
				IF EXISTS (SELECT * FROM [SSCommon].[STPostOfficeAddresses] WHERE [ContactGuid] LIKE @contactGuid AND [AddressName] LIKE @corespondenceAddressName)
					SELECT @corespondenceAddressGuid = [Guid] FROM [SSCommon].[STPostOfficeAddresses] WHERE [ContactGuid] LIKE @contactGuid AND [AddressName] LIKE @corespondenceAddressName
				ELSE
					IF (@corespondenceAddressGuid IS NULL OR @corespondenceAddressGuid LIKE '00000000-0000-0000-0000-000000000000')
						SET @corespondenceAddressGuid = NEWID()
			END
			INSERT INTO [#PostOfficeAddresses]([AddressName], [Guid], [Street], [Commune], [District], [Place], [PostCode], [PostOffice], [ApartmentNo],
				[HouseNo], [ContactGuid], [Province], [Country], [PostOfficeBox])
			SELECT CASE WHEN a.[Name] IS NULL OR a.[Name] = '' THEN 'adres korespondencyjny' ELSE a.[Name] END, @corespondenceAddressGuid, a.[Street], '', '', a.[Place], a.[PostalCode], a.[Post], a.[ApartmentNo],
				a.[HouseNo], @contactGuid, a.[Province], a.[Country], ''
			FROM [#tblCorespondenceAddress] a
		END

		-- odczytanie pozostałych adresów podpietych pod kontakt (np. adresy dostaw)
		INSERT INTO [#PostOfficeAddresses]([AddressName], [Guid], [Street], [Commune], [District], [Place], [PostCode], [PostOffice], [ApartmentNo],
			[HouseNo], [ContactGuid], [Province], [Country], [PostOfficeBox])
		SELECT [AddressName], [Guid], [Street], [Commune], [District], [Place], [PostCode], [PostOffice], [ApartmentNo],
			[HouseNo], [ContactGuid], [Province], [Country], [PostOfficeBox]
		FROM [SSCommon].[STPostOfficeAddresses]
		WHERE [ContactGuid] = @contactGuid AND [AddressName] NOT LIKE 'adres domyślny' AND [AddressName] NOT LIKE 'adres korespondencyjny'


		SET @contactTeleItContacts = (SELECT * FROM #TeleItContacts FOR XML PATH('TeleItContacts'), root ('DocumentElement'))
		SET @contactPostOfficeAddresses = (SELECT * FROM #PostOfficeAddresses FOR XML PATH('PostOfficeAddresses'), root ('DocumentElement'))
		
		EXEC [SSCommon].[sp_SaveContactWithDetails] 
					@contactId,
					@contactGuid,
					@contactName,
					@contactMainPostOfficeAddress,
					@contactMainTeleITContact,
					@contactPostOfficeAddresses,
					@contactTeleItContacts,
					@contactWriteAction,
					@contextInfo

-- zapis informacji do tabeli z danymi bankowymi
-- TODO wykorzystać procedurę sp_InsertBankingInfoAndAccounts, sp_UpdateBankingInfoAndAccounts, sp_InsertElementAndAttrs, sp_SaveElementAndAttrs, sp_UpdateElementAndAttrs jeżeli jest taka możliwość
		SET CONTEXT_INFO @ContextInfoBinaryA;

		DECLARE @removingElementsTable TABLE([ElementId] INT, [AccountId] INT) -- tymczasowa tabela na elementy do usunięcia
		IF EXISTS (SELECT * FROM [#tblBankAccounts])
		BEGIN	
			-- tabela tymczasowa na Rachunki bankowe
			CREATE TABLE [#Accounts]
			(
				[Id] INT IDENTITY(1,1),
				[Guid] UNIQUEIDENTIFIER,
				[BankingInfosGuid] UNIQUEIDENTIFIER,
				[AccountNo] NVARCHAR(50) COLLATE database_default,
				[BankName] NVARCHAR(100) COLLATE database_default,
				[AccountName] NVARCHAR(100) COLLATE database_default,
				[ElementId] INT,
				[MainElementGuid] UNIQUEIDENTIFIER,
				[SWIFT_BIC] NVARCHAR(11) COLLATE database_default,
				[Active] BIT,
				[WhiteList] BIT,
				[WhiteListDate] DATETIME2
			)

			DECLARE @bankAccountsEKId INT
			DECLARE @bankAccountsEKGuid UNIQUEIDENTIFIER = '4849512E-9024-4B7C-BD06-737BF4FF3A77'
			SELECT @bankAccountsEKId = [ElementKindId] FROM [SSCommon].[STElementKinds] WHERE [Guid] LIKE @bankAccountsEKGuid
			DECLARE @minBankAccountsPosition INT
			DECLARE @bankingId INT
			DECLARE @bankingMainElement UNIQUEIDENTIFIER
			DECLARE @bankingAccounts NVARCHAR(MAX)

			-- sprawdzenie czy kontrahent ma BankingInfo
			IF (@bankingInfoGuid IS NULL) -- nowe banking info
			BEGIN			
				SET @bankingInfoGuid = NEWID()
				EXEC [SSCommon].[sp_InsertBankingInfoAndAccounts] 
					0,
					@bankingInfoGuid,
					'',
					'<DocumentElement />',
					@contextInfoA,
					NULL			
			END
			
			-- usunięcie kont bankowych
			INSERT INTO @removingElementsTable
			SELECT cmnElements.[ElementId], cmnAccounts.[Id] FROM [SSCommon].[STElements] cmnElements
			INNER JOIN [SSCommon].[STAccounts] cmnAccounts ON cmnAccounts.[MainElementGuid] = cmnElements.[Guid]
			LEFT OUTER JOIN [#tblBankAccounts] tAccount ON tAccount.[Id] = cmnAccounts.[Id]
			WHERE tAccount.[Id] IS NULL AND cmnAccounts.[BankingInfosGuid] LIKE @bankingInfoGuid

			IF EXISTS(SELECT * FROM @removingElementsTable)
			BEGIN
				DELETE FROM [SSCommon].[STElementExAttributes]
				WHERE [ElementId] IN (SELECT [ElementId] FROM @removingElementsTable)

				UPDATE [SSCommon].[STBankingInfos]
				SET [MainAccount] = NULL
				FROM [SSCommon].[STBankingInfos] bi
				INNER JOIN #tblBankAccounts tba ON bi.[MainAccount] = tba.[Guid]
				WHERE bi.[Guid] = @bankingInfoGuid
			
				BEGIN TRY
					DELETE FROM [SSCommon].[STAccounts]
					WHERE [Id] IN (SELECT [AccountId] FROM @removingElementsTable)
			
					DELETE FROM [SSCommon].[STElements]
					WHERE [ElementId] IN (SELECT [ElementId] FROM @removingElementsTable)
				END TRY
				BEGIN CATCH
					IF (ERROR_NUMBER() = 547)
						THROW 50000, N'Nie można usunąć rachunku bankowego. Istnieją dokumenty powiązane z danym rachunkiem.', 1
					ELSE
						THROW 50000, N'Nie powiodło się usuwanie rachunków bankowych z bazy danych', 1
				END CATCH
			END		
			-- aktualizacja rachunków bankowych
			SELECT @minBankAccountsPosition = ISNULL(MIN([Position]), 0) FROM [SSCommon].[STElements] WHERE [ElementKindId] = @bankAccountsEKId
		
			BEGIN TRY
				--aktualizacja STElements
				SELECT
						@extAttAccountNo = '',
						@extAttDescription = '',
						@extAttExtAttributes = '<DocumentElement />',					
						@extAttExtOtherThenOriginal=0			
				
				DECLARE @counter INT = 0
				
				DECLARE @accountToHandle NVARCHAR(50)
				SELECT @accountToHandle = [Guid]
				FROM #tblBankAccounts
				ORDER BY [Id] ASC
				OFFSET @counter ROWS
				FETCH NEXT 1 ROWS ONLY
				
				WHILE @accountToHandle IS NOT NULL
				BEGIN
					SELECT
						@extAttElementId = CASE WHEN elem.[ElementId] IS NULL OR elem.[ElementId] = 0 THEN -1 ELSE elem.[ElementId] END,
						@extAttTitle = tAcc.[Guid],
						@extAttActive = tAcc.[IsActive],
						@extAttShortcut = tAcc.[Guid],
						@extAttPosition = @minBankAccountsPosition - 1,
						@minBankAccountsPosition = @minBankAccountsPosition - 1,
						@extAttExtWriteAction = CASE WHEN elem.[ObjVer] IS NULL THEN 1 ELSE 2 END,
						@extAttExtObjVer = CASE WHEN elem.[ElementId] IS NULL OR elem.[ElementId] = 0 THEN 0 ELSE elem.[ObjVer] + 1 END
					FROM #tblBankAccounts tAcc
					LEFT OUTER JOIN [SSCommon].[STElements] elem ON tAcc.[Guid] = elem.[Guid]
					WHERE tAcc.[Guid] = @accountToHandle

					EXEC [SSCommon].[sp_SaveElementAndAttrs] 
							@extAttElementId , 
							@accountToHandle, 
							@extAttTitle,
							@extAttActive,
							@extAttAccountNo,
							@extAttDescription,
							@bankAccountsEKId,
							@extAttShortcut,
							@extAttPosition,
							@extAttExtObjVer,
							@extAttExtAttributes,
							@bankAccountsEKGuid,
							@extAttExtWriteAction,
							@extAttExtOtherThenOriginal,
							@contextInfoA
					
					SET @accountToHandle = NULL
					SELECT @accountToHandle = [Guid],
						@counter = @counter + 1
					FROM #tblBankAccounts
					ORDER BY [Id] ASC
					OFFSET @counter ROWS
					FETCH NEXT 1 ROWS ONLY
				END

				SELECT @bankingMainElement = [Guid]
				FROM #tblBankAccounts
				WHERE [IsDefault] = 1
				--aktualizacja danych konkretnych rachunków bankowych
				INSERT INTO #Accounts ([Guid], [BankingInfosGuid], [AccountNo], [BankName],
					[Active], [SWIFT_BIC], [WhiteList], [WhiteListDate])
				SELECT 
					CASE WHEN tAccount.[Guid] IS NULL OR tAccount.[Guid] LIKE '00000000-0000-0000-0000-000000000000' THEN NEWID() ELSE tAccount.[Guid] END,
					@bankingInfoGuid, tAccount.[AccountNo], ISNULL(tAccount.[BankName], ''),
					ISNULL(tAccount.[IsActive], 1), ISNULL(tAccount.[SWIFT_BIC], ''),
					tAccount.[WhiteList], tAccount.[WhiteListDate]
				FROM #tblBankAccounts tAccount
				LEFT OUTER JOIN [SSCommon].[STAccounts] sa ON tAccount.[Guid] = sa.[Guid]
				
				UPDATE #Accounts
				SET [AccountName] = [Guid],
					[MainElementGuid] = [Guid]
				FROM #Accounts
				
				SET @bankingAccounts = (SELECT * FROM #Accounts FOR XML PATH('Accounts'), root ('DocumentElement'))
			
				EXEC [SSCommon].[sp_UpdateBankingInfoAndAccounts] 
					@bankingId,
					@bankingInfoGuid,
					'',
					@bankingAccounts,
					@contextInfoA,
					@bankingMainElement
			END TRY
			BEGIN CATCH
				THROW 50000, N'Nie powiodła się aktualizacja rachunków bankowych', 1
			END CATCH
		END
		ELSE -- jeżeli istniały rachunki bankowe to zostały usunięte (UWAGA: przypisane rachunki bankowe np. do dokumentów nie zostaną usunięte)
		BEGIN 
			IF (@bankingInfoGuid IS NOT NULL AND @bankingInfoGuid NOT LIKE '00000000-0000-0000-0000-000000000000')
			BEGIN
				BEGIN TRY
				-- usunięcie kont bankowych
				INSERT INTO @removingElementsTable
				SELECT cmnElements.[ElementId], cmnAccounts.[Id] FROM [SSCommon].[STElements] cmnElements
				INNER JOIN [SSCommon].[STAccounts] cmnAccounts ON cmnAccounts.[MainElementGuid] = cmnElements.[Guid]
				LEFT OUTER JOIN [#tblBankAccounts] tAccount ON tAccount.[Id] = cmnAccounts.[Id]
				WHERE tAccount.[Id] IS NULL AND cmnAccounts.[BankingInfosGuid] LIKE @bankingInfoGuid
				
				IF EXISTS(SELECT * FROM @removingElementsTable)
				BEGIN
					DELETE FROM [SSCommon].[STElementExAttributes]
					WHERE [ElementId] IN (SELECT [ElementId] FROM @removingElementsTable)

					UPDATE [SSCommon].[STBankingInfos]
					SET [MainAccount] = NULL
					WHERE [Guid] = @bankingInfoGuid
				
					DELETE FROM [SSCommon].[STAccounts]
					WHERE [Id] IN (SELECT [AccountId] FROM @removingElementsTable)
				
					DELETE FROM [SSCommon].[STElements]
					WHERE [ElementId] IN (SELECT [ElementId] FROM @removingElementsTable)
		
					UPDATE [SSCommon].[STContractors]
					SET [BankingInfoGuid] = NULL
					WHERE [Id] = @contractorId

					DELETE FROM [SSCommon].[STBankingInfos]
					WHERE [Guid] LIKE @bankingInfoGuid

					SET @bankingInfoGuid = NULL
				END
				END TRY
				BEGIN CATCH
					IF (ERROR_NUMBER() = 547)
						THROW 50000, N'Nie można usunąć rachunku bankowego. Istnieją dokumenty powiązane z danym rachunkiem.', 1
					ELSE
						THROW 50000, N'Nie powiodło się usuwanie rachunków bankowych z bazy danych', 1
				END CATCH
			END
		END

		SET CONTEXT_INFO @ContextInfoBinary;

		IF EXISTS (SELECT * FROM [#tblContractor])
		BEGIN
			DECLARE @contractorMainElement UNIQUEIDENTIFIER
			DECLARE @mainPersonGuid UNIQUEIDENTIFIER
			SET @mainPersonGuid = NULL

			DECLARE @contractorElementKindGuid UNIQUEIDENTIFIER = 'BC090993-0768-4487-BE6F-E44E9501734C'
			DECLARE @contractorElementKindId INT = NULL

			DECLARE @firstname NVARCHAR(50), @surname NVARCHAR(50)
			DECLARE @maxStringIdentLenght INT = 100
			SELECT @firstname = [FirstName], @surname = [Surname] FROM [#tblContractor]
			IF (@firstname IS NULL OR @firstname LIKE '')
				SELECT @firstname = [FirstName] FROM [#tblContact]
			IF (@surname IS NULL OR @surname LIKE '')
				SELECT @surname = [Surname] FROM [#tblContact]
			
			IF (@contractorId IS NULL OR @contractorId = 0)
			BEGIN
				SET @contractorMainElement = NEWID()

				IF EXISTS(SELECT TOP 1 ElementId FROM [SSCommon].[STElements] elem INNER JOIN [#tblContractor] tContractor ON elem.[Shortcut] LIKE tContractor.[Shortcut] INNER JOIN [SSCommon].[STElementKinds] ekinds ON elem.[ElementKindId] = ekinds.[ElementKindId] WHERE ekinds.[Name] LIKE 'Kontrahenci')
					THROW 50000, N'Kontrahent o podanym skrócie istnieje w bazie danych', 1

                IF @moduleCode = 'FKF'
					SET @extAttPosition = (SELECT [PositionFk] FROM #tblContractor WHERE [Id] = @contractorId)
                ELSE
                    SET @extAttPosition = NULL

                IF EXISTS(SELECT TOP 1 ElementId FROM [SSCommon].[STElements] elem INNER JOIN [SSCommon].[STElementKinds] ekinds ON elem.[ElementKindId] = ekinds.[ElementKindId] WHERE elem.Position = @extAttPosition AND ekinds.[Name] = 'Kontrahenci')
					THROW 50000, N'Kontrahent o podanej pozycji istnieje w bazie danych', 1

				BEGIN TRY	
					SELECT 
						@extAttElementId = NULL,
						@extAttAccountNo =  CASE WHEN @moduleCode = 'FKF' THEN 'COMDA#GENERATEVALIDACCOUNTNO2024' ELSE 'COMDA#RESETVALIDACCOUNTNO2008' END,
						@extAttDescription = '',
						@extAttExtObjVer = 0,
						@extAttExtWriteAction = 1,
						@extAttExtOtherThenOriginal = 0

					SELECT @extAttTitle = tContractor.[Name], 
						@extAttActive = ISNULL(tContractor.[Status], 1), 						
						@extAttShortcut = ISNULL(tContractor.[Shortcut], '')
					FROM [#tblContractor] tContractor
					
					-- exAttributes
					-- splitPayment
					INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue],
						[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
					SELECT @extAttElementId, 'splitPayment', (SELECT CASE WHEN [RequestMppTransfers] LIKE 'Wymagaj' THEN 2 ELSE (CASE WHEN [RequestMppTransfers] LIKE 'Blokada' THEN 1 ELSE 0 END) END FROM [#tblContractor]),
						'System.Int16, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089', NEWID(), 'splitPayment', 0, 0
					-- idFK
					INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue],
						[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
					SELECT @extAttElementId, 'idFK', (SELECT [IdFk] FROM [#tblContractor]),
						'System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089', NEWID(), 'idFK', 0, 0						
					-- parametrFK
					INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue],
						[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
					SELECT @extAttElementId, 'parametrFK', (SELECT ISNULL([AccountantParameter], '') FROM [#tblContractor]),
						'System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089', NEWID(), 'parametrFK', 0, 0
					-- typCeny
					INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue],
						[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
					SELECT @extAttElementId, 'typCeny', (SELECT CASE WHEN [PriceTypeId] IS NULL OR  [PriceTypeId] < 0 THEN 0 ELSE [PriceTypeId] END FROM [#tblContractor]),
						'System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089', NEWID(), 'typCeny', 0, 0
					-- rodzajCeny
					INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue],
						[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
					SELECT @extAttElementId, 'rodzajCeny', (SELECT ISNULL([SaleKindId],0) FROM [#tblContractor]),
						'System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089', NEWID(), 'rodzajCeny', 0, 0
					-- rejestr
					INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue],
						[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
					SELECT @extAttElementId, 'rejestr', (SELECT ISNULL([PayRegistryId], 0) FROM [#tblContractor]),
						'System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089', NEWID(), 'rejestr', 0, 0
					-- formaPlatnosci
					INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue],
						[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
					SELECT @extAttElementId, 'formaPlatnosci', (SELECT ISNULL([PaymentFormId], 0) FROM [#tblContractor]),
						'System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089', NEWID(), 'formaPlatnosci', 0, 0
					--	znacznik kontrahenta w FK
					INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue],
						[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
					SELECT @extAttElementId, 'znacznik', (SELECT ISNULL([FkMarkerId], 0) FROM [#tblContractor]),
						'System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089', NEWID(), 'znacznik', 0, 0
  
					SET @extAttExtAttributes = (SELECT * FROM #ExtAttributes FOR XML PATH('ElementExAttributes'), root ('DocumentElement'))
					
					EXEC [SSCommon].[sp_SaveElementAndAttrs] 
						@extAttElementId , 
						@contractorMainElement, 
						@extAttTitle,
						@extAttActive,
						@extAttAccountNo,
						@extAttDescription,
						@contractorElementKindId,
						@extAttShortcut,
						@extAttPosition,
						@extAttExtObjVer,
						@extAttExtAttributes,
						@contractorElementKindGuid,
						@extAttExtWriteAction,
						@extAttExtOtherThenOriginal,
						@contextInfo

					IF ((@surname IS NOT NULL AND @surname NOT LIKE '') OR (@firstname IS NOT NULL AND @firstname NOT LIKE ''))
					BEGIN
						SET CONTEXT_INFO @ContextInfoBinaryP;
						SET @mainPersonGuid = NEWID()
						INSERT INTO [SSCommon].[STPersons]([Firstname],[SecondName],[Surname],[BirthDate],[Guid],[StringIdent],
							[DualStringIdent1],[DualStringIdent2],[Note],[ContactGuid],[BankingInfoGuid],[DataSourceName],[DataSourceAddress],[DataProcessingAgree],[DataMarketingProcessingAgree],
							[MainElement],[ExcludedFromAnonymization],[Anonymized])
						SELECT ISNULL(@firstname, ''), '', ISNULL(@surname, ''), CAST('1753-01-01 00:00:00.000' AS DATETIME2), @mainPersonGuid, LEFT(ISNULL(tContractor.[Name], ''), @maxStringIdentLenght),
							ISNULL(tContractor.[Pesel],''), ISNULL(tContractor.[Nip],''), '', @contactGuid, @bankingInfoGuid, '', '', 0, 0,
							@contractorMainElement, 0, 0
						FROM [#tblContractor] tContractor

						INSERT INTO [SSCommon].[STPersonsClassification]([Guid], [ElementId], [CDim_UserPerson], [CDim_EmployeePerson], [CDim_ContractorContactPerson])
							VALUES(NEWID(), @mainPersonGuid, 0, 0, 1)
						SET CONTEXT_INFO @ContextInfoBinary;
					END

					INSERT INTO [SSCommon].[STContractors]([Guid],
						[Shortcut],[Name],[NIP],[PESEL],[REGON],[NIP_UE],[Note],
						[ContactGuid],[BankingInfoGuid],[LimitFlag],[LimitAmount],[LimitCurrency],
						[VIES],[Negotiation],[MainElement],[MainPerson],[VATPayerActive],
						[NaturalPerson],[LinkedUnit], [VATGroup], [JST], [IDWew])
					SELECT
						@contractorGuid, tContractor.[Shortcut], tContractor.[Name], ISNULL(tContractor.[Nip], ''), ISNULL(tContractor.[Pesel], ''), ISNULL(tContractor.[Regon], ''), ISNULL(tContractor.[NipUE], ''),
						ISNULL(tContractor.[Note], ''),@contactGuid, @bankingInfoGuid, ISNULL(tContractor.[IsLimitActive], 0), ISNULL(tContractor.[LimitValue], 0), ISNULL(tContractor.[LimitCurrency], ''),
						ISNULL(tContractor.[VIES], 0), ISNULL(tContractor.[PriceNegotiation], 0), @contractorMainElement, @mainPersonGuid, ISNULL(tContractor.[VATPayerActive], 0),
						ISNULL(tContractor.[PersonKind], 0), ISNULL(tContractor.[LinkedUnit], 0), ISNULL(tContractor.[VatGroup], 0), ISNULL(tContractor.[Jst], 0), ISNULL(tContractor.[IDWew], '')
					FROM [#tblContractor] tContractor

					SELECT @contractorId = [Id] FROM [SSCommon].[STContractors] WHERE Shortcut = @extAttShortcut

					IF ((@surname IS NOT NULL AND @surname NOT LIKE '') OR (@firstname IS NOT NULL AND @firstname NOT LIKE ''))
					BEGIN
						SET CONTEXT_INFO @ContextInfoBinaryP;
						INSERT INTO [SSCommon].[STPersonsContractors]([PersonGuid],[ContractorGuid],[PersonOrder],[Guid])
							VALUES(@mainPersonGuid, @contractorGuid, 0, NEWID())
						SET CONTEXT_INFO @ContextInfoBinary;
					END

				END TRY
				BEGIN CATCH	
					THROW 50000, N'Nie powiodło się dodawanie kontrahenta', 1
				END CATCH
			END
			ELSE
			BEGIN
			IF @moduleCode LIKE 'FKF'
				SET @extAttPosition = (SELECT [PositionFk] FROM #tblContractor WHERE [Id] = @contractorId)
            ELSE
                SET @extAttPosition = NULL
			 
			 IF EXISTS(SELECT TOP 1 ElementId FROM [SSCommon].[STElements] elem 
                        INNER JOIN [SSCommon].[STElementKinds] ekinds ON elem.[ElementKindId] = ekinds.[ElementKindId] 
                        INNER JOIN [SSCommon].[STContractors] ctr ON elem.[Guid] = ctr.[MainElement]
                        WHERE elem.Position = @extAttPosition AND ekinds.[Name] = 'Kontrahenci' AND ctr.[Id] <> @contractorId)
				THROW 50000, N'Kontrahent o podanej pozycji istnieje w bazie danych', 1
	
            BEGIN TRY
					SELECT @mainPersonGuid = [MainPerson], @contractorMainElement = [MainElement], @contractorGuid = [Guid] FROM [SSCommon].[STContractors] WHERE [Id] = @contractorId
					
					SET CONTEXT_INFO @ContextInfoBinaryP;

					IF ((@surname IS NOT NULL AND @surname NOT LIKE '') OR (@firstname IS NOT NULL AND @firstname NOT LIKE ''))
					BEGIN
						IF (@mainPersonGuid IS NULL)
						BEGIN
							SET @mainPersonGuid = NEWID()

							INSERT INTO [SSCommon].[STPersons]([Firstname],[SecondName],[Surname],[BirthDate],[Guid],[StringIdent],
								[DualStringIdent1],[DualStringIdent2],[Note],[ContactGuid],[BankingInfoGuid],[DataSourceName],[DataSourceAddress],[DataProcessingAgree],[DataMarketingProcessingAgree],
								[MainElement],[ExcludedFromAnonymization],[Anonymized])
							SELECT ISNULL(@firstname, ''), '', ISNULL(@surname, ''), CAST('1753-01-01 00:00:00.000' AS DATETIME2), @mainPersonGuid, LEFT(ISNULL(tContractor.[Name], ''), @maxStringIdentLenght),
								ISNULL(tContractor.[Pesel],''), ISNULL(tContractor.[Nip],''), '', @contactGuid, @bankingInfoGuid, '', '', 0, 0,
								@contractorMainElement, 0, 0
							FROM [#tblContractor] tContractor
														
							INSERT INTO [SSCommon].[STPersonsClassification]([Guid], [ElementId], [CDim_UserPerson], [CDim_EmployeePerson], [CDim_ContractorContactPerson])
								VALUES(NEWID(), @mainPersonGuid, 0, 0, 1)

							INSERT INTO [SSCommon].[STPersonsContractors]([PersonGuid],[ContractorGuid],[PersonOrder],[Guid])
								VALUES(@mainPersonGuid, @contractorGuid, 0, NEWID())
						END
						ELSE
						BEGIN
							UPDATE [SSCommon].[STPersons]
							SET [Firstname] = ISNULL(@firstname, ''), [Surname] = ISNULL(@surname, '')
							FROM [#tblContractor] tContractor
							WHERE [SSCommon].[STPersons].Guid LIKE @mainPersonGuid
						END
					END
					ELSE
					BEGIN
						UPDATE [SSCommon].[STContractors]
						SET [MainPerson] = NULL
						WHERE [Id] = @contractorId

						DELETE FROM [SSCommon].[STPersons]
						WHERE [Guid] LIKE @mainPersonGuid

						SET @mainPersonGuid = NULL
					END			
					
					SET CONTEXT_INFO @ContextInfoBinary;

					UPDATE [SSCommon].[STContractors]
					SET
						[Shortcut] = ISNULL(tContractor.[Shortcut], ''),
						[Name] = ISNULL(tContractor.[Name], ''),
						[NIP] = ISNULL(tContractor.[Nip], ''),
						[PESEL] = ISNULL(tContractor.[Pesel], ''),
						[REGON] = ISNULL(tContractor.[Regon], ''),
						[NIP_UE] = ISNULL(tContractor.NipUE, ''),
						[Note] = ISNULL(tContractor.Note, ''),
						[ContactGuid] = @contactGuid,
						[BankingInfoGuid] = @bankingInfoGuid,
						[LimitFlag] = ISNULL(tContractor.[IsLimitActive], 0),
						[LimitAmount] = ISNULL(tContractor.[LimitValue], 0),
						[LimitCurrency] = ISNULL(tContractor.[LimitCurrency], ''),
						[VIES] = ISNULL(tContractor.[VIES], 0),
						[Negotiation] = ISNULL(tContractor.[PriceNegotiation], 0),
						[MainPerson] = @mainPersonGuid,
						[VATPayerActive] = ISNULL(tContractor.[VATPayerActive], 0),
						[NaturalPerson] = ISNULL(tContractor.[PersonKind], 0),
						[LinkedUnit] = ISNULL(tContractor.[LinkedUnit], 0),
						[VatGroup] = ISNULL(tContractor.[VatGroup], 0),
						[Jst] = ISNULL(tContractor.[Jst], 0),
                        [IDWew] = ISNULL(tContractor.[IDWew], '')
					FROM [SSCommon].[STContractors] cmnCtrs
					INNER JOIN [#tblContractor] tContractor ON tContractor.[Id] = cmnCtrs.[Id]
					WHERE cmnCtrs.[Id] = @contractorId 
												
					SELECT @extAttElementId = elem.[ElementId]
					FROM [SSCommon].[STElements] elem
					WHERE elem.[Guid] = @contractorMainElement
					
					SELECT 
					    @extAttAccountNo = CASE WHEN @moduleCode = 'FKF' THEN 'COMDA#UPDATEVALIDACCOUNTNO2024' ELSE 'COMDA#UPDATEVALIDACCOUNTNO2008' END, 
						@extAttDescription = [Description],
						@extAttExtObjVer = [ObjVer] + 1,
						@extAttExtWriteAction = 2,
						@extAttExtOtherThenOriginal = 0
					FROM [SSCommon].[STElements] 
					WHERE [ElementId] = @extAttElementId					
				
					SELECT @extAttTitle = ISNULL(tContractor.[Name], ''), 
						@extAttActive = ISNULL(tContractor.[Status], 1), 						
						@extAttShortcut = ISNULL(tContractor.[Shortcut], '')
					FROM [#tblContractor] tContractor

					-- exAttributes
					DECLARE @exAttributeId INT
					DECLARE @exAttributeGuid UNIQUEIDENTIFIER
					DECLARE @exAttributeCLRType NVARCHAR(400)
					DECLARE @exAttributeTitle NVARCHAR(200)
					DECLARE @exAttributeAccessFlag BIT
					DECLARE @exAttributeInheritedFlag BIT

					--	splitPayment
					SET @exAttributeId = NULL
					SELECT @exAttributeId = ex.[ElementExAttrId], @exAttributeGuid = ex.[Guid], @exAttributeCLRType = ex.[OriginalCLRType],
							@exAttributeTitle = ex.[Title], @exAttributeAccessFlag = ex.[AccessFlag], @exAttributeInheritedFlag = ex.[InheritedFlag]
						FROM [SSCommon].[STElementExAttributes] ex
						INNER JOIN [SSCommon].[STElements] elem ON elem.[ElementId] = ex.[ElementId]
						INNER JOIN [SSCommon].[STContractors] ctrs ON ctrs.[MainElement] = elem.[Guid]
						WHERE ctrs.[Id] = @contractorId AND ex.[Name] LIKE 'splitPayment'
					IF (@exAttributeId IS NOT NULL)
					BEGIN
						INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue], 
							[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
						SELECT @exAttributeId, 'splitPayment', (SELECT CASE WHEN [RequestMppTransfers] LIKE 'Wymagaj' THEN 2 ELSE (CASE WHEN [RequestMppTransfers] LIKE 'Blokada' THEN 1 ELSE 0 END) END FROM [#tblContractor]),
							@exAttributeCLRType, @exAttributeGuid, @exAttributeTitle, @exAttributeInheritedFlag, @exAttributeAccessFlag
					END
					ELSE
					BEGIN					
						INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue],
							[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
						SELECT @extAttElementId, 'splitPayment', (SELECT CASE WHEN [RequestMppTransfers] LIKE 'Wymagaj' THEN 2 ELSE (CASE WHEN [RequestMppTransfers] LIKE 'Blokada' THEN 1 ELSE 0 END) END FROM [#tblContractor]),
						'System.Int16, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089', NEWID(), 'splitPayment', 0, 0
					END
					--	idFK
					SET @exAttributeId = NULL
					SELECT @exAttributeId = ex.[ElementExAttrId], @exAttributeGuid = ex.[Guid], @exAttributeCLRType = ex.[OriginalCLRType],
							@exAttributeTitle = ex.[Title], @exAttributeAccessFlag = ex.[AccessFlag], @exAttributeInheritedFlag = ex.[InheritedFlag]
						FROM [SSCommon].[STElementExAttributes] ex
						INNER JOIN [SSCommon].[STElements] elem ON elem.[ElementId] = ex.[ElementId]
						INNER JOIN [SSCommon].[STContractors] ctrs ON ctrs.[MainElement] = elem.[Guid]
						WHERE ctrs.[Id] = @contractorId AND ex.[Name] LIKE 'idFK'
					IF (@exAttributeId IS NOT NULL)
					BEGIN
						INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue], 
							[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
						SELECT @exAttributeId, 'idFK', (SELECT [IdFk] FROM [#tblContractor]),
							@exAttributeCLRType, @exAttributeGuid, @exAttributeTitle, @exAttributeInheritedFlag, @exAttributeAccessFlag
					END
					ELSE
					BEGIN
						INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue],
							[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
						SELECT @extAttElementId, 'idFK', (SELECT [IdFk] FROM [#tblContractor]),
							'System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089', NEWID(), 'idFK', 0, 0
					END
					--	parametrFK
					SET @exAttributeId = NULL
					SELECT @exAttributeId = ex.[ElementExAttrId], @exAttributeGuid = ex.[Guid], @exAttributeCLRType = ex.[OriginalCLRType],
							@exAttributeTitle = ex.[Title], @exAttributeAccessFlag = ex.[AccessFlag], @exAttributeInheritedFlag = ex.[InheritedFlag]
						FROM [SSCommon].[STElementExAttributes] ex
						INNER JOIN [SSCommon].[STElements] elem ON elem.[ElementId] = ex.[ElementId]
						INNER JOIN [SSCommon].[STContractors] ctrs ON ctrs.[MainElement] = elem.[Guid]
						WHERE ctrs.[Id] = @contractorId AND ex.[Name] LIKE 'parametrFK'
					IF (@exAttributeId IS NOT NULL)
					BEGIN					
						INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue], 
							[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
						SELECT @exAttributeId, 'parametrFK', (SELECT ISNULL([AccountantParameter], '') FROM [#tblContractor]),
							@exAttributeCLRType, @exAttributeGuid, @exAttributeTitle, @exAttributeInheritedFlag, @exAttributeAccessFlag
					END
					ELSE
					BEGIN
						INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue],
							[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
						SELECT @extAttElementId, 'parametrFK', (SELECT ISNULL([AccountantParameter], '') FROM [#tblContractor]),
							'System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089', NEWID(), 'parametrFK', 0, 0
					END
					--	typCeny
					SET @exAttributeId = NULL
					SELECT @exAttributeId = ex.[ElementExAttrId], @exAttributeGuid = ex.[Guid], @exAttributeCLRType = ex.[OriginalCLRType],
							@exAttributeTitle = ex.[Title], @exAttributeAccessFlag = ex.[AccessFlag], @exAttributeInheritedFlag = ex.[InheritedFlag]
						FROM [SSCommon].[STElementExAttributes] ex
						INNER JOIN [SSCommon].[STElements] elem ON elem.[ElementId] = ex.[ElementId]
						INNER JOIN [SSCommon].[STContractors] ctrs ON ctrs.[MainElement] = elem.[Guid]
						WHERE ctrs.[Id] = @contractorId AND ex.[Name] LIKE 'typCeny'
					IF (@exAttributeId IS NOT NULL)
					BEGIN						
						INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue], 
							[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
						SELECT @exAttributeId, 'typCeny', (SELECT CASE WHEN [PriceTypeId] IS NULL OR  [PriceTypeId] < 0 THEN 0 ELSE [PriceTypeId] END  FROM [#tblContractor]),
							@exAttributeCLRType, @exAttributeGuid, @exAttributeTitle, @exAttributeInheritedFlag, @exAttributeAccessFlag
					END
					ELSE
					BEGIN
						INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue],
							[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
						SELECT @extAttElementId, 'typCeny', (SELECT CASE WHEN [PriceTypeId] IS NULL OR  [PriceTypeId] < 0 THEN 0 ELSE [PriceTypeId] END  FROM [#tblContractor]),
							'System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089', NEWID(), 'typCeny', 0, 0
					END
					--	rodzajCeny
					SET @exAttributeId = NULL
					SELECT @exAttributeId = ex.[ElementExAttrId], @exAttributeGuid = ex.[Guid], @exAttributeCLRType = ex.[OriginalCLRType],
							@exAttributeTitle = ex.[Title], @exAttributeAccessFlag = ex.[AccessFlag], @exAttributeInheritedFlag = ex.[InheritedFlag]
						FROM [SSCommon].[STElementExAttributes] ex
						INNER JOIN [SSCommon].[STElements] elem ON elem.[ElementId] = ex.[ElementId]
						INNER JOIN [SSCommon].[STContractors] ctrs ON ctrs.[MainElement] = elem.[Guid]
						WHERE ctrs.[Id] = @contractorId AND ex.[Name] LIKE 'rodzajCeny'
					IF (@exAttributeId IS NOT NULL)
					BEGIN						
						INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue], 
							[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
						SELECT @exAttributeId, 'rodzajCeny', (SELECT ISNULL([SaleKindId],0) FROM [#tblContractor]),
							@exAttributeCLRType, @exAttributeGuid, @exAttributeTitle, @exAttributeInheritedFlag, @exAttributeAccessFlag
					END
					ELSE
					BEGIN
						INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue],
							[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
						SELECT @extAttElementId, 'rodzajCeny', (SELECT ISNULL([SaleKindId],0) FROM [#tblContractor]),
							'System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089', NEWID(), 'rodzajCeny', 0, 0
					END
					--	rejestr
					SET @exAttributeId = NULL
					SELECT @exAttributeId = ex.[ElementExAttrId], @exAttributeGuid = ex.[Guid], @exAttributeCLRType = ex.[OriginalCLRType],
							@exAttributeTitle = ex.[Title], @exAttributeAccessFlag = ex.[AccessFlag], @exAttributeInheritedFlag = ex.[InheritedFlag]
						FROM [SSCommon].[STElementExAttributes] ex
						INNER JOIN [SSCommon].[STElements] elem ON elem.[ElementId] = ex.[ElementId]
						INNER JOIN [SSCommon].[STContractors] ctrs ON ctrs.[MainElement] = elem.[Guid]
						WHERE ctrs.[Id] = @contractorId AND ex.[Name] LIKE 'rejestr'
					IF (@exAttributeId IS NOT NULL)
					BEGIN
						INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue], 
							[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
						SELECT @exAttributeId, 'rejestr', (SELECT ISNULL([PayRegistryId], 0) FROM [#tblContractor]),
							@exAttributeCLRType, @exAttributeGuid, @exAttributeTitle, @exAttributeInheritedFlag, @exAttributeAccessFlag
					END
					ELSE
					BEGIN
						INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue],
							[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
						SELECT @extAttElementId, 'rejestr', (SELECT ISNULL([PayRegistryId], 0) FROM [#tblContractor]),
							'System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089', NEWID(), 'rejestr', 0, 0
					END
					--	formaPlatnosci
					SET @exAttributeId = NULL
					SELECT @exAttributeId = ex.[ElementExAttrId], @exAttributeGuid = ex.[Guid], @exAttributeCLRType = ex.[OriginalCLRType],
							@exAttributeTitle = ex.[Title], @exAttributeAccessFlag = ex.[AccessFlag], @exAttributeInheritedFlag = ex.[InheritedFlag]
						FROM [SSCommon].[STElementExAttributes] ex
						INNER JOIN [SSCommon].[STElements] elem ON elem.[ElementId] = ex.[ElementId]
						INNER JOIN [SSCommon].[STContractors] ctrs ON ctrs.[MainElement] = elem.[Guid]
						WHERE ctrs.[Id] = @contractorId AND ex.[Name] LIKE 'formaPlatnosci'
					IF (@exAttributeId IS NOT NULL)
					BEGIN
						INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue], 
							[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
						SELECT @exAttributeId, 'formaPlatnosci', (SELECT ISNULL([PaymentFormId], 0) FROM [#tblContractor]),
							@exAttributeCLRType, @exAttributeGuid, @exAttributeTitle, @exAttributeInheritedFlag, @exAttributeAccessFlag
					END
					ELSE
					BEGIN
						INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue],
							[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
						SELECT @extAttElementId, 'formaPlatnosci', (SELECT ISNULL([PaymentFormId], 0) FROM [#tblContractor]),
							'System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089', NEWID(), 'formaPlatnosci', 0, 0
					END
					--	znacznik kontrahenta w FK
					SET @exAttributeId = NULL
					SELECT @exAttributeId = ex.[ElementExAttrId], @exAttributeGuid = ex.[Guid], @exAttributeCLRType = ex.[OriginalCLRType],
							@exAttributeTitle = ex.[Title], @exAttributeAccessFlag = ex.[AccessFlag], @exAttributeInheritedFlag = ex.[InheritedFlag]
						FROM [SSCommon].[STElementExAttributes] ex
						INNER JOIN [SSCommon].[STElements] elem ON elem.[ElementId] = ex.[ElementId]
						INNER JOIN [SSCommon].[STContractors] ctrs ON ctrs.[MainElement] = elem.[Guid]
						WHERE ctrs.[Id] = @contractorId AND ex.[Name] LIKE 'znacznik'
					IF (@exAttributeId IS NOT NULL)
					BEGIN
						INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue], 
							[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
						SELECT @exAttributeId, 'znacznik', (SELECT ISNULL([FkMarkerId], 0) FROM [#tblContractor]),
							@exAttributeCLRType, @exAttributeGuid, @exAttributeTitle, @exAttributeInheritedFlag, @exAttributeAccessFlag
					END
					ELSE
					BEGIN
						INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue],
							[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
						SELECT @extAttElementId, 'znacznik', (SELECT ISNULL([FkMarkerId], 0) FROM [#tblContractor]),
							'System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089', NEWID(), 'znacznik', 0, 1
					END

					SET @extAttExtAttributes = (SELECT * FROM #ExtAttributes FOR XML PATH('ElementExAttributes'), root ('DocumentElement'))
					
					EXEC [SSCommon].[sp_SaveElementAndAttrs] 
						@extAttElementId , 
						@contractorMainElement, 
						@extAttTitle,
						@extAttActive,
						@extAttAccountNo,
						@extAttDescription,
						@contractorElementKindId,
						@extAttShortcut,
						@extAttPosition,
						@extAttExtObjVer,
						@extAttExtAttributes,
						@contractorElementKindGuid,
						@extAttExtWriteAction,
						@extAttExtOtherThenOriginal,
						@contextInfo
				END TRY
				BEGIN CATCH
					THROW 50000, N'Nie powiodła się aktualizacja kontrahenta', 1
				END CATCH
			END
		END
		
		IF EXISTS (SELECT * FROM [#tblTradeData])
		BEGIN
			DECLARE @discountPercent FLOAT
			SELECT @discountPercent = [DiscountPercent] FROM [#tblTradeData]
			IF (@discountPercent IS NOT NULL AND @discountPercent <> 0)
			BEGIN
				IF EXISTS (SELECT * FROM [HM].[ContractorDiscounts] WHERE [ContractorId] = @contractorId)
				BEGIN
					UPDATE [HM].[CN] 
					SET [cena] = @discountPercent
					WHERE [typ] = 15 and [idpd] = @contractorId
				END
				ELSE
				BEGIN
					INSERT INTO [HM].[CN]([idpd], [cena], [typ], [flag], [subtyp], [idpm], [typpd], [idck], [typpar], [parametr], [typceny], [waluta], [aktywna], [flagcen], [datastart], [dataend], [createdBy])
					VALUES (@contractorId, @discountPercent, 15, 0, 0, NULL, 0, NULL, 0, 0, 0, '%', 0, 0, NULL, NULL, NULL)
				END
			END
			ELSE
			BEGIN
				DELETE FROM [HM].[CN]
				WHERE [idpd] = @contractorId AND [typ] = 15
			END
		END

		DECLARE @markerId INT		
		DECLARE @contractorKindId INT
		DECLARE @catalogId NVARCHAR(100)
		SELECT @catalogId = [CatalogId], @contractorKindId = ISNULL([ContractorKindId], 0), @markerId = ISNULL([MarkerId], 0)
			FROM [#tblContractor]

		DECLARE @contractorKindGuid UNIQUEIDENTIFIER
		IF (@contractorKindId <> 0)
			SELECT @contractorKindGuid = [Guid] FROM [SSCommon].[STElements] WHERE [ElementId] = @contractorKindId
		
		DECLARE @catalogGuid UNIQUEIDENTIFIER
		IF (@catalogId IS NOT NULL AND @catalogId NOT LIKE '')
			SELECT @catalogGuid = elem.[Guid] 
			FROM [SSCommon].[STElements] elem
			JOIN [SSCommon].[STElementKinds] elemKind ON elem.[ElementKindId] = elemKind.[ElementKindId] AND elemKind.Shortcut ='KatalogKh'
			WHERE elem.[Shortcut] = @catalogId

		DECLARE @markerGuid UNIQUEIDENTIFIER
		IF (@markerId IS NOT NULL AND @markerId <> 0)
			SELECT @markerGuid = [Guid] FROM [SSCommon].[STElements] WHERE [ElementId] = @markerId

		IF EXISTS (SELECT * FROM [#tblCustomFields])
		BEGIN
			--SELECT * FROM [#tblCustomFields]
			DECLARE @valueStatement NVARCHAR(MAX)
			DECLARE @newLine NVARCHAR(10)
			SET @newLine = char(13)+char(10)

			DECLARE @setStatement NVARCHAR(MAX)
			SELECT @setStatement = COALESCE(@setStatement + ', ', '') +
					'[' + [ColumnName] + '] = ' + 
					CASE WHEN [Value] IS NULL THEN 'NULL' 
					ELSE (CASE WHEN ([Type] = 'System.Boolean, mscorlib') THEN (CASE WHEN [Value] = 'True' THEN '1' ELSE '0' END)
						ELSE (CASE WHEN ([Type] = 'System.DateTime, mscorlib') THEN 'CAST(''' + [Value] + '''AS datetime2)'
							ELSE (CASE WHEN ([Type] = 'System.String, mscorlib') THEN '''' + [Value] + ''''
								ELSE [Value] END) END) END) END
			FROM [#tblCustomFields]
			--SELECT @setStatement

			DECLARE @insertColumnsStatement NVARCHAR(MAX)
			DECLARE @insertValuesStatement NVARCHAR(MAX)
			SELECT @insertColumnsStatement = COALESCE(@insertColumnsStatement + ', ', '') + '[' + [ColumnName] + ']',
				@insertValuesStatement = COALESCE(@insertValuesStatement + ', ', '') +
					CASE WHEN [Value] IS NULL THEN 'NULL' 
					ELSE (CASE WHEN ([Type] = 'System.Boolean, mscorlib') THEN (CASE WHEN [Value] = 'True' THEN '1' ELSE '0' END)
						ELSE (CASE WHEN ([Type] = 'System.DateTime, mscorlib') THEN 'CAST(''' + [Value] + '''AS datetime2)'
							ELSE (CASE WHEN ([Type] = 'System.String, mscorlib') THEN '''' + [Value] + ''''
								ELSE [Value] END) END) END) END
			FROM [#tblCustomFields]
			--SELECT @insertColumnsStatement
			--SELECT @insertValuesStatement
			
			SET @valueStatement = CONCAT('IF EXISTS(SELECT * FROM [SSCommon].[',@classificationTableName,'] WHERE [ElementId] = ',@contractorId,')',
				@newLine,'BEGIN',
					@newLine,'UPDATE [SSCommon].[',@classificationTableName,']',
					@newLine,'SET ',
					@newLine,@setStatement,
					@newLine,'WHERE [ElementId] = ',@contractorId,
				@newLine,'END',
				@newLine,'ELSE',
				@newLine,'BEGIN',
					@newLine,'INSERT INTO [SSCommon].[',@classificationTableName,']([ElementId], [Guid],',@insertColumnsStatement,')',
					@newLine,'VALUES(',@contractorId,',''',NEWID(),''',',@insertValuesStatement,')',
				@newLine,'END')

			--SELECT @valueStatement
			EXEC (@valueStatement)
		END
		ELSE
		BEGIN
			DECLARE @deleteStatement NVARCHAR(MAX)

			SET @deleteStatement = CONCAT('DELETE FROM [SSCommon].[', @classificationTableName,'] WHERE [ElementId] = ''', @contractorId, '''')
			--SELECT @deleteStatement
			EXEC (@deleteStatement)
		END

		IF NOT EXISTS(SELECT * FROM [SSCommon].[STContractorsClassification] WHERE [ElementId] LIKE @contractorGuid)
		BEGIN
			INSERT INTO [SSCommon].[STContractorsClassification]([Guid],[ElementId],[CDim_ZnacznikKh],[CDim_RodzajKh],[CDim_KatalogKh])
			VALUES (NEWID(), @contractorGuid, @markerGuid, @contractorKindGuid, @catalogGuid)
		END
		ELSE
		BEGIN
			UPDATE [SSCommon].[STContractorsClassification]
			SET [CDim_ZnacznikKh] = @markerGuid, [CDim_RodzajKh] = @contractorKindGuid, [CDim_KatalogKh] = @catalogGuid
			WHERE [ElementId] LIKE @contractorGuid
		END

		--uprawnienia do grup 
		--deserializacja z xml
		INSERT INTO [#ContractorsGroupPermissions]
		SELECT 
			HasRight,ElementId,Name		
		FROM 
			OPENXML(@handle, '/Contractor/ContractorsGroupPermissions/ContractorsGroupPermission')
			WITH
			(
				[HasRight] BIT 'HasRight',
				[Name] NVARCHAR(100) 'Name',
				[ElementId] INT 'ElementId'
				
			)
			
		-- dopisanie kontrahenta do grupy
		DELETE crg FROM [SSCommon].[STContractorsRightsGroups] crg
		JOIN [SSCommon].[STUserRightsGroups] urg on urg.ContractorRightGroup = crg.ContractorRightGroup
		WHERE ContractorId=@contractorId AND UserId = @userid;

		INSERT INTO [SSCommon].[STContractorsRightsGroups](ContractorId,ContractorRightGroup)
		SELECT @contractorId, ElementId FROM #ContractorsGroupPermissions WHERE HasRight=1	


		--czyścimy śmieci
		IF (OBJECT_ID('tempdb..[#tblContractor]') IS NOT NULL) DROP TABLE [#tblContractor]
		IF (OBJECT_ID('tempdb..[#tblContact]') IS NOT NULL) DROP TABLE [#tblContact]
		IF (OBJECT_ID('tempdb..[#tblMainAddress]') IS NOT NULL) DROP TABLE [#tblMainAddress]
		IF (OBJECT_ID('tempdb..[#tblCorespondenceAddress]') IS NOT NULL) DROP TABLE [#tblCorespondenceAddress]
		IF (OBJECT_ID('tempdb..[#tblTradeData]') IS NOT NULL) DROP TABLE [#tblTradeData]
		IF (OBJECT_ID('tempdb..[#tblBankAccounts]') IS NOT NULL) DROP TABLE [#tblBankAccounts]
		IF (OBJECT_ID('tempdb..[#tblCustomFields]') IS NOT NULL) DROP TABLE [#tblCustomFields]
		IF (OBJECT_ID('tempdb..[#ContractorsGroupPermissions]') IS NOT NULL) DROP TABLE [#ContractorsGroupPermissions]
		
		EXEC [Common].[UpdateContractorRIFromXML] @contractorId, @contractorData, @moduleCode , @contextInfo, @contextInfoP

		COMMIT TRANSACTION;

		exec [Common].[ReadContractorAsXML] @contractorId, @moduleCode, @userid

	END TRY
	BEGIN CATCH
		IF @@TRANCOUNT > 0
			ROLLBACK TRANSACTION;
		THROW;
	END CATCH
END

GO

CREATE OR ALTER PROCEDURE HM.UpdateKsefRelatedPartiesBranch
    @documentId INT,
    @receiverAddressId INT,
    @contractorAddressId INT,
    @subtype INT = NULL
AS
BEGIN
    BEGIN TRY

        IF @documentId = 0
            RETURN;

        --sprawdzamy czy dokument jest dokumentem KSeF
        IF (HM.fnIsKsefDocumentType(@subtype) = 0)
            RETURN;

    BEGIN TRANSACTION

        -- Aktualizacja (usunięcie/dodanie) podmiotów powiązanych dla sprzedawcy wystawiającego fakturę w imieniu (Jednostki Samorządu Terytorialnego)
        EXEC HM.UpdateKsefRelatedPartiesJstSeller
            @documentId = @documentId,
            @subtype = @subtype

        -- odział w podmiotach powiazanych
        -- (id wpisu, id oddziału, dodany przez uzytkownika w oknie podmioty powiazane)
        DECLARE @branches TABLE 
        (
            Id int, 
            BranchId INT, 
            AddedByUser BIT, 
            [Role] INT, 
            IdentifierType INT,
            PRIMARY KEY CLUSTERED (Id),
            INDEX IX_BranchId_Role_AddedByUser_IdentifierType NONCLUSTERED (BranchId, [Role], AddedByUser, IdentifierType)
         )

        INSERT INTO @branches (Id, BranchId, AddedByUser, [Role], IdentifierType)
        SELECT Id, ISNULL(BranchId, -1), AddedByUser, [Role], IdentifierType
        FROM HM.Ksef_OtherParties
         --    biezacy dokument             oddział        odbiorca/JST/GV      
        WHERE DocumentId = @documentId AND PartyType = 1 AND [Role] IN (2, 8, 10)

        DECLARE @receiverId INT
        DECLARE @branchId INT
        DECLARE @adrName VARCHAR(150)
        DECLARE @receiverIDWew VARCHAR(5)
        DECLARE @email NVARCHAR(100) 
        DECLARE @phone NVARCHAR(50)

        --pobranie id odbiorcy oraz numeru oddziału KSeF
        SELECT TOP 1 @receiverId = idkh, @adrName = nazwaAdr, @receiverIDWew = LTRIM(RTRIM(REPLACE(REPLACE(ad.KsefDepartmentValue, '-', ''), ' ', ''))),
                    @email = ISNULL(ad.EmailThirdParty, ''), @phone = ISNULL(IIF(ISNULL(LEN(TRIM(ad.TelThirdParty)), 0) > 16, NULL, TRIM(ad.TelThirdParty)), '')
        FROM HM.AD WHERE Id = @receiverAddressId

        DECLARE @BranchToDeleteIdList HM.IntListTypeUnique

        IF (ISNULL(@receiverAddressId, 0) = ISNULL(@contractorAddressId, 0))
        BEGIN
            
            INSERT INTO @BranchToDeleteIdList (Id)
            SELECT DISTINCT Id 
            FROM @branches p
            WHERE p.BranchId IS NOT NULL AND p.AddedByUser = 0

            -- usuwanie wpisów z podmiotów powiązanych
            EXEC [HM].[DeleteKsefRelatedPartiesByIdList] @BranchToDeleteIdList
            
            COMMIT TRANSACTION
            RETURN;
        END

        -- pobranie id odziału KSeF
        SELECT TOP 1 @branchId = s.Id
        FROM SSCommon.STContractors AS Contractors0
        INNER JOIN SSCommon.STContacts contact ON contact.Guid = Contractors0.ContactGuid
        INNER JOIN SSCommon.STPostOfficeAddresses s ON s.ContactGuid = contact.Guid
            AND s.AddressName = @adrName
        WHERE Contractors0.Id = @receiverId

        --pobranie kraju odbiorcy
        DECLARE @receiverCountry CHAR(2)
        SELECT TOP 1 @receiverCountry = s.Country
        FROM SSCommon.STContractors AS Contractors0
        INNER JOIN SSCommon.STContacts contact ON contact.Guid = Contractors0.ContactGuid
        INNER JOIN SSCommon.STPostOfficeAddresses s ON s.ContactGuid = contact.Guid
	        AND s.Guid = contact.MainPostOfficeAddress
        WHERE Contractors0.Id = @receiverId

        -- brak może wynikać z usunięcia adresu dostawy (oddziału ksef)
        SET @branchId = ISNULL(@branchId, 0)

        --pobranie id kontrahenta, czy jest członkiem Grupy VAT lub Jednostką Samorządu Terytorialnego (JST). 
        DECLARE @Buyer_JST BIT = 0
        DECLARE @Buyer_VG BIT = 0
        DECLARE @contractorId INT
        SELECT 
            @contractorId = a.idkh,
            @Buyer_JST = c.JST,
            @Buyer_VG = c.VATGroup
        FROM HM.AD a
        INNER JOIN SSCommon.STContractors c ON c.Id = a.idkh
        WHERE a.Id = @contractorAddressId

        DECLARE @InsertThirdPartySetting INT
        SELECT TOP 1 @InsertThirdPartySetting = SettingSelector
        FROM HM.KsefSettings_Additional
        WHERE SettingType = 24      -- ustawienie: Odbiorca w podmiotach powiązanych = 24

		-- Nie dodawaj odbiorcy do Podmiotów powiązanych (opcja domyślna)
		SET @InsertThirdPartySetting = ISNULL(@InsertThirdPartySetting, 0)

        --Czy aktualne ustawienie Ksef ma wstawiać identyfikator (nip, nipUe, wewnętrzny, zewnętrzny)
        DECLARE @HasIdentifierType INT = IIF(@InsertThirdPartySetting = 2, 1, 0) -- 2 oznacza dodaj z indentyfikatorem

        -- odbiorca jest z IDWew lub Oddziałem KSeF
		SET @receiverIDWew = ISNULL(@receiverIDWew, '')
        DECLARE @InsertReceiverIDWew BIT = IIF(@receiverIDWew != '' AND @InsertThirdPartySetting = 0, 1, 0)

        --sprawdzenie czy wygenerowany stan jest aktualny w stosunku do JST / GV / IDWew oraz ustawień KSeF tak by można było skończyć przetwarzanie
        DECLARE @Role__2_OK BIT = 0
        DECLARE @Role__8_OK BIT = 0
        DECLARE @Role_10_OK BIT = 0

        DECLARE @InsertRole_2 BIT = IIF(@Buyer_JST = 0 AND @Buyer_VG = 0 AND (@InsertThirdPartySetting != 0 OR @InsertReceiverIDWew = 1), 1, 0)
        IF @InsertRole_2 = 1 AND EXISTS (SELECT TOP 1 1 FROM @branches WHERE BranchId = @branchId AND AddedByUser = 0 AND [Role] = 2 AND ((IdentifierType = 0 AND @HasIdentifierType = 0) OR (IdentifierType <> 0 AND @HasIdentifierType = 1))) OR
           @InsertRole_2 = 0 AND NOT EXISTS (SELECT TOP 1 1 FROM @branches WHERE BranchId = @branchId AND AddedByUser = 0 AND [Role] = 2 AND ((IdentifierType = 0 AND @HasIdentifierType = 0) OR (IdentifierType <> 0 AND @HasIdentifierType = 1) OR @InsertThirdPartySetting = 0))
        BEGIN
            SET @Role__2_OK = 1 -- automatyczne wpisy dotyczące roli 2 są prawidłowe
        END

        IF @Buyer_JST = 1 AND EXISTS (SELECT TOP 1 1 FROM @branches WHERE BranchId = @branchId AND AddedByUser = 0 AND [Role] = 8 AND ((IdentifierType = 0 AND @HasIdentifierType = 0) OR (IdentifierType <> 0 AND @HasIdentifierType = 1))) OR
           @Buyer_JST = 0 AND NOT EXISTS (SELECT TOP 1 1 FROM @branches WHERE BranchId = @branchId AND AddedByUser = 0 AND [Role] = 8 AND ((IdentifierType = 0 AND @HasIdentifierType = 0) OR (IdentifierType <> 0 AND @HasIdentifierType = 1)))
        BEGIN
            SET @Role__8_OK = 1 -- automatyczne wpisy dotyczące roli 8 są prawidłowe
        END

        IF @Buyer_VG = 1 AND EXISTS (SELECT TOP 1 1 FROM @branches WHERE BranchId = @branchId AND AddedByUser = 0 AND [Role] = 10 AND ((IdentifierType = 0 AND @HasIdentifierType = 0) OR (IdentifierType <> 0 AND @HasIdentifierType = 1))) OR
           @Buyer_VG = 0 AND NOT EXISTS (SELECT TOP 1 1 FROM @branches WHERE BranchId = @branchId AND AddedByUser = 0 AND [Role] = 10 AND ((IdentifierType = 0 AND @HasIdentifierType = 0) OR (IdentifierType <> 0 AND @HasIdentifierType = 1)))
        BEGIN
            SET @Role_10_OK = 1 -- automatyczne wpisy dotyczące roli 10 są prawidłowe
        END

        IF NOT EXISTS (SELECT TOP 1 1 FROM @branches WHERE BranchId != @branchId AND AddedByUser = 0) AND	-- zmieniono adres => zmienił się @branchId => należy usunąć poprzednie automatyczne wpisy
			(@Role__2_OK = 1 AND @Role__8_OK = 1 AND @Role_10_OK = 1 OR										-- wpisy z rolami 2/8/10 ok
            NOT EXISTS (SELECT TOP 1 1 FROM @branches WHERE AddedByUser = 0) AND @branchId = 0)				-- dokument nie ma odziału w podmiotach powiazanych i nie wskazano odziału
        BEGIN
            COMMIT TRANSACTION
            RETURN;
        END

        -- z podmiotow powiazanych usuwamy oddziały inne niz wskazany ?? Lub gdy JST/GV nie pasuje do nabywcy i IDWew dla odbiorcy
        INSERT INTO @BranchToDeleteIdList (Id)
        SELECT DISTINCT p.Id
        FROM @branches AS p
        WHERE 
            (p.BranchId <> @branchId AND p.AddedByUser = 0)
            OR
            (
                p.BranchId = @branchId AND 
                p.AddedByUser = 0 AND
                (
                    (
                        (p.[Role] = 8 AND @Buyer_JST = 0) 
                        OR 
                        (p.[Role] = 10 AND @Buyer_VG = 0) 
                        OR 
                        (
                            (@Buyer_JST = 1 OR @Buyer_VG = 1 OR @InsertReceiverIDWew = 0) 
                            AND 
                            p.[Role] = 2
                        )
                    )
                    OR
                    (
                        (p.IdentifierType = 0 AND @HasIdentifierType = 1) 
                        OR 
                        (p.IdentifierType <> 0 AND @HasIdentifierType = 0)
                    )
                ) 
            )

        -- usuwanie wpisów z podmiotów powiązanych
        EXEC [HM].[DeleteKsefRelatedPartiesByIdList] @BranchToDeleteIdList

        DELETE br
        FROM @branches br
        INNER JOIN @BranchToDeleteIdList ids ON ids.Id = br.Id

        -- ===== OBSŁUGA WSTAWIANIA ODBIORCY DO PODMIOTÓW POWIĄZANYCH ===== --

        --Obsługa ustawienia Odbiorca - czy dodawać odbiorcę do podmiotów powiązanych
        IF @InsertThirdPartySetting = 0 AND @Buyer_JST = 0 AND @Buyer_VG = 0 AND @InsertReceiverIDWew = 0 -- Nie dodawaj odbiorcy do podmiotów powiązanych
        BEGIN
            COMMIT TRANSACTION
            RETURN;
        END

        IF @branchId = 0 --brak id odd
        BEGIN
            COMMIT TRANSACTION
            RETURN;
        END

        -- odczytujemy dane odziału
        DECLARE @name NVARCHAR(512),
                @nip NVARCHAR(20),
                @nip_ue NVARCHAR(20), 
                @country CHAR(2), 
                @vies BIT, 
                @address NVARCHAR(512),
                @gln NVARCHAR(13),
                @eori NVARCHAR(256);
            
        IF EXISTS (SELECT TOP 1 1 FROM HM.KSef_Departments AS d WHERE d.STPostOfficeAddressesId = @branchId)
        BEGIN
            --Pobieranie danych oddziału
            SELECT 
                    @name = TRIM(c.Name), -- wysyłamy nazwę kontrahenta
                    @nip = TRIM(c.NIP), 
                    @nip_ue = TRIM(c.NIP_UE), 
                    @country = SUBSTRING(TRIM(a.Country), 1, 2), 
                    @vies = c.VIES,
                    @address = HM.GetFullAddress(a.Street, a.HouseNo, a.ApartmentNo, a.PostCode, a.Place),
                    @gln = TRIM(d.GLN),
                    @eori = TRIM(d.EORI),
                    @email = d.EmailThirdParty,
                    @phone = IIF(ISNULL(LEN(TRIM(d.TelThirdParty)), 0) > 16, NULL, TRIM(d.TelThirdParty))
            FROM HM.KSef_Departments AS d
            INNER JOIN SSCommon.STPostOfficeAddresses a ON a.Id = d.STPostOfficeAddressesId
            INNER JOIN SSCommon.STContacts ct ON ct.Guid = a.ContactGuid
            INNER JOIN SSCommon.STContractors AS c ON c.ContactGuid = ct.Guid
            WHERE d.STPostOfficeAddressesId = @branchId 
        END
        ELSE
        BEGIN
            -- EORI
            EXEC HM.GetContractorEori
                    @contractorId = @receiverId,
                    @value        = @eori OUTPUT;
                 
            -- GLN
            EXEC HM.GetContractorGln
                    @contractorId = @receiverId,
                    @value        = @gln OUTPUT;

            --Pobieranie danych dla adresu domyślnego
            SELECT 
                    @name = TRIM(c.Name),
                    @nip = TRIM(c.NIP), 
                    @nip_ue = TRIM(c.NIP_UE), 
                    @country = SUBSTRING(TRIM(a.Country), 1, 2), 
                    @vies = c.VIES,
                    @address = HM.GetFullAddress(a.Street, a.HouseNo, a.ApartmentNo, a.PostCode, a.Place)
            FROM SSCommon.STPostOfficeAddresses a 
            INNER JOIN SSCommon.STContacts ct ON ct.Guid = a.ContactGuid
            INNER JOIN SSCommon.STContractors AS c ON c.ContactGuid = ct.Guid
            WHERE a.Id = @branchId
        END

        -- okreslamy typ identyfikatora ( NIP / VAT-UE / Brak )
        DECLARE @identyfierType INT = 0     -- Brak
        DECLARE @normalizedNipUE NVARCHAR(20) = HM.fnNormalizeNip(@nip_ue, @receiverCountry, @vies, 1, 1);
        DECLARE @normalizedNip NVARCHAR(20) = HM.fnNormalizeNip(@nip, @receiverCountry, @vies, 0, 1);
        DECLARE @otherTaxId NVARCHAR(100)

        DECLARE @InternalId VARCHAR(40)
        IF ISNULL(LEN(TRIM(@normalizedNip)), 0) > 0 AND ISNULL(LEN(TRIM(@receiverIDWew)), 0) > 0
            SET @InternalId = TRIM(@normalizedNip) + '-' + TRIM(@receiverIDWew)

		DECLARE @isCountryFromEu INT
		SELECT @isCountryFromEu = IsFromUe FROM HM.fnIsCountryFromUe(@receiverCountry)

        IF ISNULL(LEN(TRIM(@InternalId)), 0) > 0
            SET @identyfierType = 4 -- Wewnętrzny
        ELSE IF ISNULL(LEN(TRIM(@normalizedNip)), 0) > 0 AND @receiverCountry = 'PL'
            SET @identyfierType = 1 -- NIP
        ELSE IF ISNULL(LEN(TRIM(@normalizedNipUE)), 0) > 0 AND @receiverCountry <> 'PL' AND @vies = 1 AND @isCountryFromEu = 1
            SET @identyfierType = 2 -- VatUE
        ELSE IF ISNULL(LEN(TRIM(@nip)), 0) > 0 
        BEGIN
            SET @identyfierType = 3 -- Identyfikator podatkowy inny.
            IF (LEFT(@nip, 2) = @receiverCountry)
                SET @otherTaxId = SUBSTRING(@nip, 3, LEN(@nip) - 2);
            ELSE
                SET @otherTaxId = @nip
        END
        ELSE
            SET @identyfierType = 0 -- Nieokreślony (Brak)

        IF @InsertThirdPartySetting = 1 -- Dodaj Odbiorcę dokumentu do Podmiotów powiązanych bez numeru NIP
        BEGIN
            SET @identyfierType = 0 -- Nieokreślony (Brak)
        END

        IF @Buyer_JST = 0 AND @Buyer_VG = 0 AND (@InsertThirdPartySetting != 0 OR @InsertReceiverIDWew != 0)
        BEGIN
            -- brak odziału w podmiotach powiazanych - dodajemy wskazanego
            INSERT INTO HM.Ksef_OtherParties (
                DocumentId,
                PartyType,
                BranchId,
                ContractorId,
                Name,
                Role,
                InternalId,
                IdentifierType,
                Nip,
                NipUE,
                OtherTaxId,
                Eori,
                CountryCode,
                Address,
                Gln,
                Email,
                Phone,
                AddedByUser
            )
            VALUES (
                @documentId,
                1,
                @branchId,
                @receiverId,
                @name,
                2,
                @InternalId,
                @identyfierType,
                @normalizedNip,
                @normalizedNipUE,
                @otherTaxId,
                @eori,
                @country,
                @address,
                @gln,
                @email,
                @phone,
                0
            );
        END
        ELSE
        BEGIN
            IF @Buyer_JST = 1 AND NOT EXISTS (SELECT TOP 1 1 FROM @branches WHERE BranchId = @branchId AND AddedByUser = 0 AND [Role] = 8)
            BEGIN
                -- brak odbiorcy w podmiotach powiazanych - dodajemy wskazanego
                INSERT INTO HM.Ksef_OtherParties (
                    DocumentId,
                    PartyType,
                    BranchId,
                    Name,
                    Role,
                    InternalId,
                    IdentifierType,
                    Nip,
                    NipUE,
                    OtherTaxId,
                    Eori,
                    CountryCode,
                    Address,
                    Gln,
                    Email,
                    Phone,
                    AddedByUser
                )
                VALUES (
                    @documentId,
                    1,
                    @branchId,
                    @name,
                    8,
                    @InternalId,
                    @identyfierType,
                    @normalizedNip,
                    @normalizedNipUE,
                    @otherTaxId,
                    @eori,
                    @country,
                    @address,
                    @gln,
                    @email,
                    @phone,
                    0
                );
            END

            IF @Buyer_VG = 1 AND NOT EXISTS (SELECT TOP 1 1 FROM @branches WHERE BranchId = @branchId AND AddedByUser = 0 AND [Role] = 10)
            BEGIN
                -- brak odbiorcy w podmiotach powiazanych - dodajemy wskazanego
                INSERT INTO HM.Ksef_OtherParties (
                    DocumentId,
                    PartyType,
                    BranchId,
                    Name,
                    Role,
                    InternalId,
                    IdentifierType,
                    Nip,
                    NipUE,
                    OtherTaxId,
                    Eori,
                    CountryCode,
                    Address,
                    Gln,
                    Email,
                    Phone,
                    AddedByUser
                )
                VALUES (
                    @documentId,
                    1,
                    @branchId,
                    @name,
                    10,
                    @InternalId,
                    @identyfierType,
                    @normalizedNip,
                    @normalizedNipUE,
                    @otherTaxId,
                    @eori,
                    @country,
                    @address,
                    @gln,
                    @email,
                    @phone,
                    0
                );
            END
        END

    	COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		IF @@TRANCOUNT > 0
			ROLLBACK TRANSACTION;
		THROW;
	END CATCH
END

GO
