

CREATE PROCEDURE dbo.CSAPP_UPDATE_CUSTOMER_FROM_PROTOCOL @protocolId BIGINT, @new BIT OUTPUT
AS
BEGIN
	DECLARE @customer VARCHAR(255) = (SELECT TOP 1 FIELD_VALUE FROM CSAPP_PROTOCOL WHERE FIELD_NAME = 'CUSTOMER' AND PROTOCOL_PK = @protocolId)
	DECLARE @name1 VARCHAR(255) = (SELECT TOP 1 FIELD_VALUE FROM CSAPP_PROTOCOL WHERE FIELD_NAME = 'NAME' AND PROTOCOL_PK = @protocolId)
	DECLARE @street VARCHAR(255) = (SELECT TOP 1 FIELD_VALUE FROM CSAPP_PROTOCOL WHERE FIELD_NAME = 'STREET' AND PROTOCOL_PK = @protocolId)
	DECLARE @code VARCHAR(255) = (SELECT TOP 1 FIELD_VALUE FROM CSAPP_PROTOCOL WHERE FIELD_NAME = 'CODE' AND PROTOCOL_PK = @protocolId)
	DECLARE @town VARCHAR(255) = (SELECT TOP 1 FIELD_VALUE FROM CSAPP_PROTOCOL WHERE FIELD_NAME = 'TOWN' AND PROTOCOL_PK = @protocolId)
	DECLARE @email VARCHAR(255) = (SELECT TOP 1 FIELD_VALUE FROM CSAPP_PROTOCOL WHERE FIELD_NAME = 'MAIL' AND PROTOCOL_PK = @protocolId)
	DECLARE @emailUsage VARCHAR(255) = (SELECT TOP 1 FIELD_VALUE FROM CSAPP_PROTOCOL WHERE FIELD_NAME = 'MAILUSAGE' AND PROTOCOL_PK = @protocolId)
    
	IF EXISTS(SELECT * FROM KUNDEN WHERE NUMMER = @customer) AND @customer <> 0 AND @customer IS NOT null
	BEGIN  
		DECLARE @name1Customer VARCHAR(255)= (SELECT TOP 1 NAME1 FROM KUNDEN WHERE nummer = @customer)
		DECLARE @streetCustomer VARCHAR(255) = (SELECT TOP 1 STRASSE FROM KUNDEN WHERE nummer = @customer)
		DECLARE @codeCustomer VARCHAR(255) = (SELECT TOP 1 PLZ FROM KUNDEN WHERE nummer = @customer)
		DECLARE @townCustomer VARCHAR(255) = (SELECT TOP 1 ORT FROM KUNDEN WHERE nummer = @customer)

		IF (@name1 <> @name1Customer) OR (@street <> @streetCustomer)  OR (@code <> @codeCustomer) OR (@town <> @townCustomer)
			SELECT @new = 1
		ELSE
			SELECT @new = 0      

		UPDATE KUNDEN SET NAME1 = ISNULL(@name1, NAME1), STRASSE = ISNULL(@street, STRASSE), PLZ = ISNULL(@code, PLZ),
		ORT = ISNULL(@town, ORT), EMAIL = CASE WHEN @emailUsage = '1' THEN @email ELSE EMAIL END
		WHERE  NUMMER =@customer
	END
	ELSE
	IF (@name1 IS NOT NULL AND @name1 <> '') OR (@street IS NOT NULL AND @street <> '') OR (@code IS NOT NULL AND @name1 <> '') OR (@town IS NOT NULL AND @town <> '')
	BEGIN
  SET  @customer =   (SELECT pk FROM dbo.next_pks WHERE Tablename = 'KPK')
		INSERT INTO dbo.KUNDEN
		        ( NUMMER ,
		          NAME1 ,
		          STRASSE ,
		          PLZ ,
		          ORT ,
				  EMAIL
		        )
		VALUES  ( @customer, -- NUMMER - int
		          @name1 , -- NAME1 - varchar(50)
		          @street , -- STRASSE - varchar(50)
		          @code , -- PLZ - varchar(30)
		          @town,  -- ORT - varchar(50)
				  CASE WHEN @emailUsage = '1' THEN @email ELSE null END
		          )
	UPDATE dbo.next_pks SET pk = pk +1 WHERE Tablename = 'KPK'
	SELECT	@new = 1
	END  
	EXEC dbo.aenderungseintrag_Kunden @kdnummer = @customer, -- int
	    @benutzer = 'API' -- varchar(30)
	
END