SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [SSCommon].[sp_SaveContactWithDetails]
@Id int OUTPUT,
@Guid uniqueidentifier OUTPUT,
@Name nvarchar(100) OUTPUT,
@MainPostOfficeAddress uniqueidentifier OUTPUT,
@MainTeleITContact uniqueidentifier OUTPUT,
@PostOfficeAddresses nvarchar(max),
@TeleItContacts nvarchar(max),
@WriteAction int OUTPUT,
@ContextInfo varchar(128)
 AS

--Wykonuje Insert lub Update kontaktu z detalami
--W przypadku Update nie zmieniamy Id i Guid kontaktu

declare @ContextInfoBinary as varbinary(128)
SET @ContextInfoBinary = convert(varbinary(128),@ContextInfo)
SET CONTEXT_INFO @ContextInfoBinary;

--?CommonStatement.ContextInfoParsing

--Rozstrzyganie Insert czy Update

IF @Guid = 0x0
	SET @Guid = NULL

IF @Id = -1
	SET @Id = NULL

IF @Id IS NULL AND @Guid IS NULL
BEGIN
	IF @WriteAction = 0
		SET @WriteAction = 1 --New
	ELSE
		IF @WriteAction = 2 --Update
		BEGIN
			RAISERROR('Update and both identifiers are null',16,1)
			RETURN
		END
END
ELSE
BEGIN
	IF @Guid IS NULL
		SELECT @Guid = [Guid] FROM [SSCommon].[STContacts] WHERE [Id] = @Id
END

IF @WriteAction = 0
BEGIN
	IF @Guid IS NOT NULL AND EXISTS (SELECT 1 FROM [SSCommon].[STContacts] WHERE [Guid] = @Guid)
		SET @WriteAction = 2
	ELSE
		SET @WriteAction = 1
END

IF @WriteAction = 1 AND @Guid IS NULL --New
	SET @Guid = NEWID()

--Zapis

IF @WriteAction = 1 --New
BEGIN
	INSERT INTO [SSCommon].[STContacts]([Guid], [Name])
		VALUES (@Guid, @Name)

	SELECT @Id=IDENT_CURRENT('[SSCommon].[STContacts]')
END
ELSE
BEGIN
	UPDATE [SSCommon].[STContacts]
		SET [Name]=@Name
			WHERE [Guid]=@Guid

	SELECT @Id = [Id] FROM [SSCommon].[STContacts] WHERE [Guid] = @Guid
END

DECLARE @xml XML;

--TeleItContacts

IF @TeleItContacts IS NOT NULL
BEGIN
	SET @xml = CONVERT(XML, @TeleItContacts, 1);

	IF @WriteAction = 1
	BEGIN
		--New
		INSERT INTO [SSCommon].[STTeleItContacts] ([Guid], [Name], [ContactGuid], [Telephone1], [Telephone2], [Telephone3], [Fax], [Fax2], [Telex], [Email1], [Email2], [WWW], [Communicator1], [Communicator2], [Description],[Facebook])
		 	SELECT c.value('(Guid/text())[1]', 'uniqueidentifier'), 
			ISNULL(c.value('(Name/text())[1]', 'nvarchar(50)'), ''), 
			@Guid,
			ISNULL(c.value('(Telephone1/text())[1]', 'nvarchar(50)'), ''), 
			ISNULL(c.value('(Telephone2/text())[1]', 'nvarchar(50)'), ''), 
			ISNULL(c.value('(Telephone3/text())[1]', 'nvarchar(50)'), ''), 
			ISNULL(c.value('(Fax/text())[1]', 'nvarchar(50)'), ''), 
			ISNULL(c.value('(Fax2/text())[1]', 'nvarchar(50)'), ''), 
			ISNULL(c.value('(Telex/text())[1]', 'nvarchar(50)'), ''), 
			ISNULL(c.value('(Email1/text())[1]', 'nvarchar(100)'), ''), 
			ISNULL(c.value('(Email2/text())[1]', 'nvarchar(100)'), ''), 
			ISNULL(c.value('(WWW/text())[1]', 'nvarchar(100)'), ''), 
			ISNULL(c.value('(Communicator1/text())[1]', 'nvarchar(50)'), ''), 
			ISNULL(c.value('(Communicator2/text())[1]', 'nvarchar(50)'), ''), 
			ISNULL(c.value('(Description/text())[1]', 'nvarchar(3000)'), ''), 
			ISNULL(c.value('(Facebook/text())[1]', 'nvarchar(100)'), '')
			FROM @xml.nodes('/DocumentElement/TeleItContacts') AS T(c);
			
		IF @MainTeleITContact IS NOT NULL
		BEGIN
			UPDATE [SSCommon].[STContacts] SET [MainTeleITContact] = @MainTeleITContact WHERE [Guid] = @Guid
		END
	END
	ELSE
	BEGIN
		--Update
		CREATE TABLE #tic(
		[Guid] [uniqueidentifier] NOT NULL,
		[Name] [nvarchar](50) COLLATE database_default NOT NULL,
		[ContactGuid] [uniqueidentifier] NOT NULL,
		[Telephone1] [nvarchar](50) COLLATE database_default NOT NULL,
		[Telephone2] [nvarchar](50) COLLATE database_default NOT NULL,
		[Telephone3] [nvarchar](50) COLLATE database_default NOT NULL,
		[Fax] [nvarchar](50) COLLATE database_default NOT NULL,
		[Fax2] [nvarchar](50) COLLATE database_default NOT NULL,
		[Telex] [nvarchar](50) COLLATE database_default NOT NULL,
		[Email1] [nvarchar](100) COLLATE database_default NOT NULL,
		[Email2] [nvarchar](100) COLLATE database_default NOT NULL,
		[WWW] [nvarchar](100) COLLATE database_default NOT NULL,
		[Communicator1] [nvarchar](50) COLLATE database_default NOT NULL,
		[Communicator2] [nvarchar](50) COLLATE database_default NOT NULL,
		[Description] [nvarchar](3000) COLLATE database_default NOT NULL,
		[Facebook] [nvarchar](100) COLLATE database_default NOT NULL);

		INSERT INTO #tic SELECT 
			c.value('(Guid/text())[1]', 'uniqueidentifier'), 
			ISNULL(c.value('(Name/text())[1]', 'nvarchar(50)'), ''), 
			@Guid,
			ISNULL(c.value('(Telephone1/text())[1]', 'nvarchar(50)'), ''), 
			ISNULL(c.value('(Telephone2/text())[1]', 'nvarchar(50)'), ''), 
			ISNULL(c.value('(Telephone3/text())[1]', 'nvarchar(50)'), ''), 
			ISNULL(c.value('(Fax/text())[1]', 'nvarchar(50)'), ''), 
			ISNULL(c.value('(Fax2/text())[1]', 'nvarchar(50)'), ''), 
			ISNULL(c.value('(Telex/text())[1]', 'nvarchar(50)'), ''), 
			ISNULL(c.value('(Email1/text())[1]', 'nvarchar(100)'), ''), 
			ISNULL(c.value('(Email2/text())[1]', 'nvarchar(100)'), ''), 
			ISNULL(c.value('(WWW/text())[1]', 'nvarchar(100)'), ''), 
			ISNULL(c.value('(Communicator1/text())[1]', 'nvarchar(50)'), ''), 
			ISNULL(c.value('(Communicator2/text())[1]', 'nvarchar(50)'), ''), 
			ISNULL(c.value('(Description/text())[1]', 'nvarchar(3000)'), ''), 
			ISNULL(c.value('(Facebook/text())[1]', 'nvarchar(100)'), '')
			FROM @xml.nodes('/DocumentElement/TeleItContacts') AS T(c);
	
		--UPDATE #tic SET [ContactGuid] = @Guid

		--Identyfikacja po Guid lub Nazwie
		--Guid nie zmieniamy
		DELETE FROM [SSCommon].[STTeleItContacts]
			WHERE [ContactGuid] = @Guid
					AND [Guid] NOT IN (SELECT [Guid] FROM #tic )
					AND [Name] NOT IN (SELECT [Name] FROM #tic )
				
		UPDATE [SSCommon].[STTeleItContacts]
			SET [Name] = t.[Name], --[ContactGuid] = t.[ContactGuid],
				[Telephone1] = t.[Telephone1], [Telephone2] = t.[Telephone2], [Telephone3] = t.[Telephone3],
				[Fax] = t.[Fax], [Fax2] = t.[Fax2], [Telex] = t.[Telex], [Email1] = t.[Email1], [Email2] = t.[Email2], [WWW] = t.[WWW],
				[Communicator1] = t.[Communicator1], [Communicator2] = t.[Communicator2], [Description] = t.[Description], [Facebook] = t.[Facebook]
			FROM [SSCommon].[STTeleItContacts] a
			join #tic t	on a.[Guid] = t.[Guid]

		UPDATE [SSCommon].[STTeleItContacts]
			SET [Name] = t.[Name], --[ContactGuid] = t.[ContactGuid],
				[Telephone1] = t.[Telephone1], [Telephone2] = t.[Telephone2], [Telephone3] = t.[Telephone3],
				[Fax] = t.[Fax], [Fax2] = t.[Fax2], [Telex] = t.[Telex], [Email1] = t.[Email1], [Email2] = t.[Email2], [WWW] = t.[WWW],
				[Communicator1] = t.[Communicator1], [Communicator2] = t.[Communicator2], [Description] = t.[Description], [Facebook] = t.[Facebook]
			FROM [SSCommon].[STTeleItContacts] a
			join #tic t on a.[Guid] <> t.[Guid] AND a.[ContactGuid] = t.[ContactGuid] AND a.[Name] = t.[Name]
		
		INSERT INTO [SSCommon].[STTeleItContacts] ([Guid], [Name], [ContactGuid], [Telephone1], [Telephone2], [Telephone3], [Fax], [Fax2], [Telex], [Email1], [Email2], [WWW], [Communicator1], [Communicator2], [Description], [Facebook])
		 	SELECT * FROM #tic a WHERE NOT EXISTS (SELECT [Guid] FROM [SSCommon].[STTeleItContacts] b
				WHERE a.[Guid] = b.[Guid] OR (a.[ContactGuid] = b.[ContactGuid] AND a.[Name] = b.[Name]) )

		DROP TABLE #tic
		
		UPDATE [SSCommon].[STContacts] SET [MainTeleITContact] = @MainTeleITContact WHERE [Guid] = @Guid
	END
END

--PostOfficeAddresses

IF @PostOfficeAddresses IS NOT NULL
BEGIN
	SET @xml = CONVERT(XML, @PostOfficeAddresses, 1);

	IF @WriteAction = 1
	BEGIN
		--New
		INSERT INTO [SSCommon].[STPostOfficeAddresses] ([AddressName], [Guid], [Street], [Commune], [District], [Place], [PostCode], [PostOffice], [ApartmentNo], [HouseNo], [ContactGuid], [Province], [Country], [CountryElementGuid], [PostOfficeBox])
		 	SELECT 
			ISNULL(c.value('(AddressName/text())[1]', 'nvarchar(64)'), ''), 
			c.value('(Guid/text())[1]', 'uniqueidentifier'),
			ISNULL(c.value('(Street/text())[1]', 'nvarchar(50)'), ''), 
			ISNULL(c.value('(Commune/text())[1]', 'nvarchar(50)'), ''), 
			ISNULL(c.value('(District/text())[1]', 'nvarchar(50)'), ''), 
			ISNULL(c.value('(Place/text())[1]', 'nvarchar(50)'), ''), 
			ISNULL(c.value('(PostCode/text())[1]', 'nvarchar(10)'), ''), 
			ISNULL(c.value('(PostOffice/text())[1]', 'nvarchar(50)'), ''), 
			ISNULL(c.value('(ApartmentNo/text())[1]', 'nvarchar(15)'), ''), 
			ISNULL(c.value('(HouseNo/text())[1]', 'nvarchar(15)'), ''), 
			@Guid, 
			ISNULL(c.value('(Province/text())[1]', 'nvarchar(50)'), ''), 
			ISNULL(c.value('(Country/text())[1]', 'nvarchar(50)'), ''), 
			c.value('(CountryElementGuid/text())[1]', 'uniqueidentifier'),
			ISNULL(c.value('(PostOfficeBox/text())[1]', 'nvarchar(50)'), '') 
			FROM @xml.nodes('/DocumentElement/PostOfficeAddresses') AS T(c);
			
		IF @MainPostOfficeAddress IS NOT NULL
		BEGIN
			UPDATE [SSCommon].[STContacts] SET [MainPostOfficeAddress] = @MainPostOfficeAddress WHERE [Guid] = @Guid
		END			
	END
	ELSE
	BEGIN
		--Update
		CREATE TABLE #poa(
		[AddressName] [nvarchar](64) COLLATE database_default NOT NULL,
		[Guid] [uniqueidentifier] NOT NULL,
		[Street] [nvarchar](50) COLLATE database_default NOT NULL,
		[Commune] [nvarchar](50) COLLATE database_default NOT NULL,
		[District] [nvarchar](50) COLLATE database_default NOT NULL,
		[Place] [nvarchar](50) COLLATE database_default NOT NULL,
		[PostCode] [nvarchar](10) COLLATE database_default NOT NULL,
		[PostOffice] [nvarchar](50) COLLATE database_default NOT NULL,
		[ApartmentNo] [nvarchar](15) COLLATE database_default NOT NULL,
		[HouseNo] [nvarchar](15) COLLATE database_default NOT NULL,
		[ContactGuid] [uniqueidentifier] NOT NULL,
		[Province] [nvarchar](50) COLLATE database_default NOT NULL,
		[Country] [nvarchar](50) COLLATE database_default NOT NULL,
		[CountryElementGuid] [uniqueidentifier] NULL,
		[PostOfficeBox] [nvarchar](50) COLLATE database_default NOT NULL);

		INSERT INTO #poa SELECT 
			ISNULL(c.value('(AddressName/text())[1]', 'nvarchar(64)'), ''), 
			c.value('(Guid/text())[1]', 'uniqueidentifier'),
			ISNULL(c.value('(Street/text())[1]', 'nvarchar(50)'), ''), 
			ISNULL(c.value('(Commune/text())[1]', 'nvarchar(50)'), ''), 
			ISNULL(c.value('(District/text())[1]', 'nvarchar(50)'), ''), 
			ISNULL(c.value('(Place/text())[1]', 'nvarchar(50)'), ''), 
			ISNULL(c.value('(PostCode/text())[1]', 'nvarchar(10)'), ''), 
			ISNULL(c.value('(PostOffice/text())[1]', 'nvarchar(50)'), ''), 
			ISNULL(c.value('(ApartmentNo/text())[1]', 'nvarchar(15)'), ''), 
			ISNULL(c.value('(HouseNo/text())[1]', 'nvarchar(15)'), ''), 
			@Guid, 
			ISNULL(c.value('(Province/text())[1]', 'nvarchar(50)'), ''), 
			ISNULL(c.value('(Country/text())[1]', 'nvarchar(50)'), ''), 
			c.value('(CountryElementGuid/text())[1]', 'uniqueidentifier'),
			ISNULL(c.value('(PostOfficeBox/text())[1]', 'nvarchar(50)'), '') 
			FROM @xml.nodes('/DocumentElement/PostOfficeAddresses') AS T(c);
	
		--UPDATE #poa SET [ContactGuid]=@Guid

		--Identyfikacja po Guid lub Nazwie
		--Guid nie zmieniamy
		DELETE FROM [SSCommon].[STPostOfficeAddresses]
			WHERE [ContactGuid] = @Guid
				AND	[Guid] NOT IN (SELECT [Guid] FROM #poa )
				AND [AddressName] NOT IN (SELECT [AddressName] FROM #poa )
		
		UPDATE [SSCommon].[STPostOfficeAddresses]
			SET --[ContactGuid] = t.[ContactGuid]
				[AddressName] = t.[AddressName], [Street] = t.[Street], [Commune] = t.[Commune], [District] = t.[District],
				[Place] = t.[Place], [PostCode] = t.[PostCode], [PostOffice] = t.[PostOffice], [ApartmentNo] = t.[ApartmentNo],
				[HouseNo] = t.[HouseNo], [Province] = t.[Province], [Country] = t.[Country], [CountryElementGuid] = t.[CountryElementGuid], 
				[PostOfficeBox] = t.[PostOfficeBox] 
			FROM [SSCommon].[STPostOfficeAddresses] a
			join #poa t on a.[Guid] = t.[Guid]

		UPDATE [SSCommon].[STPostOfficeAddresses]
			SET --[ContactGuid] = t.[ContactGuid]
				[AddressName] = t.[AddressName], [Street] = t.[Street], [Commune] = t.[Commune], [District] = t.[District],
				[Place] = t.[Place], [PostCode] = t.[PostCode], [PostOffice] = t.[PostOffice], [ApartmentNo] = t.[ApartmentNo],
				[HouseNo] = t.[HouseNo], [Province] = t.[Province], [Country] = t.[Country], [CountryElementGuid] = t.[CountryElementGuid], 
				[PostOfficeBox] = t.[PostOfficeBox] 
			FROM [SSCommon].[STPostOfficeAddresses] a
			join #poa t on a.[ContactGuid] = t.[ContactGuid] AND a.[AddressName] = t.[AddressName] AND a.[Guid] <> t.[Guid] 
		
		INSERT INTO [SSCommon].[STPostOfficeAddresses] ([AddressName], [Guid], [Street], [Commune], [District], [Place], [PostCode], [PostOffice], [ApartmentNo], [HouseNo], [ContactGuid], [Province], [Country], [CountryElementGuid], [PostOfficeBox])
			SELECT * FROM #poa a WHERE NOT EXISTS (SELECT 1 FROM [SSCommon].[STPostOfficeAddresses] b
				WHERE a.[Guid] = b.[Guid] OR(a.[ContactGuid] = b.[ContactGuid] AND a.[AddressName] = b.[AddressName]))
		
		DROP TABLE #poa
		
		UPDATE [SSCommon].[STContacts] SET [MainPostOfficeAddress] = @MainPostOfficeAddress WHERE [Guid] = @Guid
	END
END
GO

ALTER PROCEDURE [SSCommon].[sp_UpdateElementAndAttrs]
@ElementId int OUTPUT,
@Guid uniqueidentifier OUTPUT,
@Title nvarchar (200) OUTPUT,
@Active bit OUTPUT,
@AccountNo nvarchar (40) OUTPUT,
@Description nvarchar (4000) OUTPUT,
@ElementKindId int OUTPUT,
@Shortcut nvarchar (100) OUTPUT,
@Position int OUTPUT,
@ObjVer int OUTPUT,
@ElementKindGuid uniqueidentifier OUTPUT,
@ExtAttributes nvarchar(max),
@OtherThenOriginal smallint OUTPUT, 
@ContextInfo varchar(128)
AS

DECLARE @TitleOriginal as nvarchar(200)
DECLARE @ActiveOriginal as bit
DECLARE @AccountNoOriginal as nvarchar(40)
DECLARE @DescriptionOriginal as nvarchar(4000)
DECLARE @ElementKindIdOriginal as int
DECLARE @ShortcutOriginal as nvarchar(100)
DECLARE @PositionOriginal as int

declare @ContextInfoBinary as varbinary(128)
SET @ContextInfoBinary = convert(varbinary(128),@ContextInfo)
SET CONTEXT_INFO @ContextInfoBinary;

--?CommonStatement.ContextInfoParsing

--Dostajemy: @ElementId lub @Guid oraz: @Title, @Active, @AccountNo, @Description
--@Shortcut może być null, wtedy nie zmieniamy
--Doczytujemy nie zmieniane

--Jeśli jest @Guid to odczytujemy @ElementId bo może być z innej bazy -> replikacja
IF @Guid IS NULL OR @Guid = 0x0
	SELECT @Guid = [Guid], @TitleOriginal = [Title], @ActiveOriginal = [Active], @AccountNoOriginal = [AccountNo], @DescriptionOriginal = [Description], @ElementKindIdOriginal = [ElementKindId], @ShortcutOriginal = [Shortcut], @PositionOriginal = [Position] 
	FROM [SSCommon].[STElements] WHERE [ElementId] = @ElementId
ELSE
	SELECT @ElementId = ElementId, @TitleOriginal = [Title], @ActiveOriginal = [Active], @AccountNoOriginal = [AccountNo], @DescriptionOriginal = [Description], @ElementKindIdOriginal = [ElementKindId], @ShortcutOriginal = [Shortcut], @PositionOriginal = [Position] 
	FROM [SSCommon].[STElements] WHERE [Guid] = @Guid

--DRUT nie pozwalamy na zmianę pozycji dla FKF
--IF EXISTS (SELECT 1 FROM [SSCommon].[STElementsRef] WHERE [Guid] = @Guid AND [App] = 'FKF')
--BEGIN
--	SELECT @Position = NULL
--	SELECT @AccountNo = NULL
	--RAISERROR ('Element jest wykorzystywany w aplikacji FK Forte',16,1)
	--RETURN
--END
--KONIEC DRUTA

DECLARE @existingObjVer as int
SELECT @existingObjVer = [ObjVer] FROM [SSCommon].[STElements] WHERE [Guid] = @Guid

-- IF (@existingObjVer > @ObjVer)
-- BEGIN
-- 	RAISERROR ('OBJ_VER_TOO_LOW',16,1)
-- 	RETURN
-- END

IF @existingObjVer >= @ObjVer
BEGIN
	SET @ObjVer = @existingObjVer + 1
END

DECLARE @existingPosition as int
SELECT @existingPosition = [Position] FROM [SSCommon].[STElements] WHERE [Guid] = @Guid

DECLARE @OriginalAccoutNo nvarchar (40)
SET @OriginalAccoutNo = @AccountNo

IF @existingPosition>0
BEGIN
	IF @AccountNo='COMDA#RESETVALIDACCOUNTNO2008'
	BEGIN
		SELECT @Position = (SELECT TOP 1 [Position]
		FROM [SSCommon].[STElements]
		WHERE [ElementKindId] = @ElementKindId
		ORDER BY [Position] ASC)

		IF (@Position > 0) 
		BEGIN
			SET @Position = 0
		END

		SELECT @Position = (ISNULL(@Position,0)-1)
		SELECT @AccountNo = ''
	END
	ELSE IF (@AccountNo='COMDA#EXIMTOOL2008' AND @Position > 0)
	BEGIN
		SELECT @AccountNo = CONVERT(nvarchar(40), @Position)
	END
	ELSE
	BEGIN
		SELECT @Position = NULL
		SELECT @AccountNo = NULL
	END
END
ELSE IF @AccountNo='COMDA#GENERATEVALIDACCOUNTNO2008'
BEGIN
	SELECT @Position = (SELECT TOP 1 [Position]
	FROM [SSCommon].[STElements]
	WHERE [ElementKindId] = @ElementKindId
	ORDER BY [Position] DESC)

	IF (@Position < 0) 
	BEGIN
		SET @Position = 0
	END

	IF (@Position >= 999999)
	BEGIN
		RAISERROR ('FKF_Position_999999',16,1)
		RETURN
	END

	SELECT @Position = (ISNULL(@Position,0)+1)
	SELECT @AccountNo = CONVERT(nvarchar(40), @Position)
END
ELSE IF (@AccountNo='COMDA#UPDATEVALIDACCOUNTNO2008' OR @AccountNo='COMDA#EXIMTOOL2008')
BEGIN
	IF (@Position < 0 AND @existingPosition < 0)
	BEGIN
		SET @Position = @existingPosition
	END
	
	SELECT @AccountNo = CONVERT(nvarchar(40), @Position)
END
ELSE IF @Position > 0
BEGIN

    DECLARE @st_replication_id AS uniqueidentifier
    SET @st_replication_id = '00000000-0000-0000-0000-000000000000'
    
    DECLARE @divpos1 AS int
    SET @divpos1 = CHARINDEX('@', @ContextInfo);
    DECLARE @divpos2 AS int
    SET @divpos2 = CHARINDEX('$', @ContextInfo);   
	        
    SET @st_replication_id = SUBSTRING(@ContextInfo, @divpos1+1, @divpos2-@divpos1-1);
    
	DECLARE @divpos6 AS int
	SET @divpos6 = CHARINDEX('|', @ContextInfo)
	
    DECLARE @is_mirror_db_guid AS char(1)
    SET @is_mirror_db_guid = '0'	
    
    SET @is_mirror_db_guid = SUBSTRING(@ContextInfo, @divpos6+1, LEN(@ContextInfo) - @divpos6)
    
    DECLARE @Count AS int
    SET @Count = 1
    
    IF @is_mirror_db_guid='0' AND @st_replication_id <> '00000000-0000-0000-0000-000000000000'
    BEGIN			
		SELECT @Count = (SELECT COUNT(*) FROM [SSCommon].[STElements]
		WHERE [ElementKindId] = @ElementKindId AND [Position] = @Position)
	END    
    
    IF @Count = 0
    BEGIN
		SELECT @AccountNo = CONVERT(nvarchar(40), @Position)
	END
	ELSE
    BEGIN
		SELECT @Position = NULL
		--KSZ:Pozwalam na zmianę AccountNo gdy pozycja ujemna
		--SELECT @AccountNo = NULL
	END
END
ELSE
BEGIN
	SELECT @Position = NULL
	--KSZ:Pozwalam na zmianę AccountNo gdy pozycja ujemna
	--SELECT @AccountNo = NULL
END

UPDATE [SSCommon].[STElements]
SET [ObjVer] = @ObjVer, [Title] = @Title, [Active] = @Active, [AccountNo] = ISNULL(@AccountNo,[AccountNo]), [Description] = @Description, [Shortcut] = ISNULL(@Shortcut,[Shortcut]), [Position] = ISNULL(@Position, [Position]) 
WHERE [Guid] = @Guid

SELECT @ElementId=[ElementId], @Guid = [Guid], @AccountNo = [AccountNo], @ElementKindId = [ElementKindId], @Shortcut = [Shortcut], @Position = [Position]
FROM [SSCommon].[STElements] WHERE [Guid] = @Guid

SELECT @ElementKindGuid = [Guid] FROM [SSCommon].[STElementKinds] WHERE [ElementKindId] = @ElementKindId

IF @TitleOriginal <> @Title OR @ActiveOriginal <> @Active OR @AccountNoOriginal <> @AccountNo OR @DescriptionOriginal <> @Description OR @ElementKindIdOriginal <> @ElementKindId OR @ShortcutOriginal <> @Shortcut OR @PositionOriginal <> @Position
	BEGIN
		SET @OtherThenOriginal = 1
		
		IF @ActiveOriginal <> @Active
			SET @OtherThenOriginal = 2
	END
ELSE
	SET @OtherThenOriginal = 0

--INSERT na potrzeby odświeżania wartości w klasyfikacjach
IF LEFT(@OriginalAccoutNo, 6) <>'COMDA#'
	INSERT INTO [SSCommon].[STClasificationsToRefresh]([ClasificationId],[ElementId],[State]) VALUES (NULL, @ElementId, 0)

	
IF @ExtAttributes IS NOT NULL
	BEGIN
	DECLARE @xml XML;
	SET @xml = CONVERT(XML, @ExtAttributes, 1);

	CREATE TABLE #temp(
		[ElementId] [int] NOT NULL,
		[Name] [nvarchar](50) COLLATE database_default NOT NULL,
		[StringValue] [nvarchar](1024) COLLATE database_default NOT NULL,
		[OriginalCLRType] [nvarchar](400) COLLATE database_default NOT NULL,
		[ElementExAttrId] [int] NOT NULL,
		[Guid] [uniqueidentifier] NOT NULL,
		[Title] [nvarchar](200) COLLATE database_default NOT NULL,
		[AccessFlag] [bit] NOT NULL,
		[InheritedFlag] [bit] NOT NULL);

	INSERT INTO #temp SELECT c.value('(ElementId/text())[1]', 'int'), 
	ISNULL(c.value('(Name/text())[1]', 'nvarchar(50)'), ''), 
	ISNULL(c.value('(StringValue/text())[1]', 'nvarchar(1024)'), ''), 
	c.value('(OriginalCLRType/text())[1]', 'nvarchar(400)'), 
	c.value('(ElementExAttrId/text())[1]', 'int'), 
	c.value('(Guid/text())[1]', 'uniqueidentifier'), 
	ISNULL(c.value('(Title/text())[1]', 'nvarchar(200)'), ''), 
	c.value('(AccessFlag/text())[1]', 'bit'), 
	c.value('(InheritedFlag/text())[1]', 'bit')
	from @xml.nodes('/DocumentElement/ElementExAttributes') AS T(c);

	UPDATE #temp SET ElementId=@ElementId

	DELETE FROM [SSCommon].[STElementExAttributes]
			WHERE [ElementId] = @ElementId
				AND	[Name] NOT IN (SELECT [Name] FROM #temp )			
		
	UPDATE [SSCommon].[STElementExAttributes]
		SET --[ContactGuid] = t.[ContactGuid]
			[StringValue] = t.[StringValue], [OriginalCLRType] = t.[OriginalCLRType],
			[Title] = t.[Title], [AccessFlag] = t.[AccessFlag], [InheritedFlag] = t.[InheritedFlag]
		FROM [SSCommon].[STElementExAttributes] a
		join #temp t ON a.[ElementId] = @ElementId AND a.[Name] = t.[Name] 

	INSERT INTO [SSCommon].[STElementExAttributes] (ElementId, Name, StringValue, OriginalCLRType, Guid, Title, AccessFlag, InheritedFlag)
		SELECT ElementId, Name, StringValue, OriginalCLRType, Guid, Title, AccessFlag, InheritedFlag FROM #temp a WHERE NOT EXISTS (SELECT 1 FROM [SSCommon].[STElementExAttributes] b
			WHERE b.[ElementId] = @ElementId AND b.[Name] = a.[Name]) 

	DROP TABLE #temp
	END
ELSE
	BEGIN
		DELETE FROM SSCommon.STElementExAttributes WHERE [ElementId] = @ElementId
	END
GO

ALTER  PROCEDURE [SSCommon].[sp_UpdateBankingInfoAndAccounts]
@Id int OUTPUT,
@Guid uniqueidentifier OUTPUT,
@Name nvarchar(100) OUTPUT,
@Accounts nvarchar(max),
@ContextInfo varchar(128),
@MainAccount uniqueidentifier OUTPUT
 AS

declare @ContextInfoBinary as varbinary(128)
SET @ContextInfoBinary = convert(varbinary(128),@ContextInfo)
SET CONTEXT_INFO @ContextInfoBinary;

--?CommonStatement.ContextInfoParsing

UPDATE [SSCommon].[STBankingInfos]
SET [Name] = @Name WHERE [Guid] = @Guid

SELECT @Id=[Id] FROM [SSCommon].[STBankingInfos] WHERE [Guid] = @Guid

--DELETE FROM [SSCommon].[STAccounts] WHERE [BankingInfosGuid] = @Guid

DECLARE @doc int

IF @Accounts IS NOT NULL
BEGIN
DECLARE @xml XML;
SET @xml = CONVERT(XML, @Accounts, 1);

CREATE TABLE #temp(
	[Guid] [uniqueidentifier] NOT NULL,
	[BankingInfosGuid] [uniqueidentifier] NOT NULL,
	[AccountNo] [nvarchar](50) COLLATE database_default NOT NULL,
	[BankName] [nvarchar](100) COLLATE database_default NOT NULL,
	[AccountName] [nvarchar](100) COLLATE database_default NOT NULL,
	[ElementId] [int] NULL,
	[MainElementGuid] [uniqueidentifier] NOT NULL,
	[Active] [bit] NOT NULL,
	[SWIFT_BIC] [nvarchar](11) COLLATE database_default NOT NULL,
	[WhiteList] [bit] NULL,
	[WhiteListDate] [datetime] NULL);

INSERT INTO #temp SELECT 
	c.value('(Guid/text())[1]', 'uniqueidentifier'),
	c.value('(BankingInfosGuid/text())[1]', 'uniqueidentifier'),
	ISNULL(c.value('(AccountNo/text())[1]', 'nvarchar(50)'), ''), 
	ISNULL(c.value('(BankName/text())[1]', 'nvarchar(100)'), ''), 
	ISNULL(c.value('(AccountName/text())[1]', 'nvarchar(100)'), ''), 
	c.value('(ElementId/text())[1]', 'int'),
	c.value('(MainElementGuid/text())[1]', 'uniqueidentifier'),
	c.value('(Active/text())[1]', 'bit'),
	ISNULL(c.value('(SWIFT_BIC/text())[1]', 'nvarchar(11)'), ''), 
	c.value('(WhiteList/text())[1]', 'bit'),
	c.value('(WhiteListDate/text())[1]', 'datetime')
	FROM @xml.nodes('/DocumentElement/Accounts') AS T(c);

UPDATE  [SSCommon].[STAccounts] 
	SET AccountNo = t.AccountNo, BankName = t.BankName,  AccountName = t.AccountName, ElementId = t.ElementId, Active = t.Active, SWIFT_BIC = t.SWIFT_BIC, WhiteList = t.WhiteList, WhiteListDate = t.WhiteListDate
	FROM
	#temp t 
WHERE [SSCommon].[STAccounts].Guid = t.Guid 


INSERT INTO [SSCommon].[STAccounts] ([Guid], [BankingInfosGuid], [AccountNo], [BankName], [AccountName], [ElementId], [MainElementGuid], [Active], [SWIFT_BIC], [WhiteList], [WhiteListDate])
 	SELECT * FROM
	#temp a WHERE NOT EXISTS (SELECT GUID FROM  [SSCommon].[STAccounts] b WHERE a.GUID = b.GUID )

UPDATE [SSCommon].[STBankingInfos] SET [MainAccount]=@MainAccount WHERE [Guid] = @Guid

SELECT MainElementGuid INTO #tempElements FROM [SSCommon].[STAccounts] 
WHERE MainElementGuid is not null and [BankingInfosGuid] = @Guid 
AND GUID NOT IN (SELECT GUID FROM #temp)

DELETE FROM [SSCommon].[STAccounts] WHERE [BankingInfosGuid] = @Guid AND GUID NOT IN
(
 	SELECT GUID FROM
	#temp
)

DELETE FROM [SSCommon].[STElements] WHERE [Guid] IN (SELECT MainElementGuid FROM #tempElements)

DROP TABLE #temp
DROP TABLE #tempElements
END
GO