
CREATE PROCEDURE dbo.CSAPP_UPDATE_CAR_FROM_PROTOCOL @protocolId BIGINT, @new BIT OUTPUT
AS
BEGIN
	DECLARE @vin VARCHAR(255) = (SELECT TOP 1 FIELD_VALUE FROM CSAPP_PROTOCOL WHERE FIELD_NAME = 'VIN' AND PROTOCOL_PK = @protocolId)
	DECLARE @mva VARCHAR(255) = (SELECT TOP 1 FIELD_VALUE FROM CSAPP_PROTOCOL WHERE FIELD_NAME = 'MVANUMBER' AND PROTOCOL_PK = @protocolId)

	DECLARE @owner VARCHAR(255) = (SELECT TOP 1 FIELD_VALUE FROM CSAPP_PROTOCOL WHERE FIELD_NAME = 'OWNER' AND PROTOCOL_PK = @protocolId)
	DECLARE @latitude VARCHAR(255) = (SELECT TOP 1 FIELD_VALUE FROM CSAPP_PROTOCOL WHERE FIELD_NAME = 'LATITUDE' AND PROTOCOL_PK = @protocolId)
	DECLARE @longitude VARCHAR(255) = (SELECT TOP 1 FIELD_VALUE FROM CSAPP_PROTOCOL WHERE FIELD_NAME = 'LONGITUDE' AND PROTOCOL_PK = @protocolId)
	DECLARE @km VARCHAR(255) = (SELECT TOP 1 FIELD_VALUE FROM CSAPP_PROTOCOL WHERE FIELD_NAME = 'KM' AND PROTOCOL_PK = @protocolId)
	DECLARE @fuel VARCHAR(255) = (SELECT TOP 1 FIELD_VALUE FROM CSAPP_PROTOCOL WHERE FIELD_NAME = 'TANK' AND PROTOCOL_PK = @protocolId)
	DECLARE @license VARCHAR(255) = (SELECT TOP 1 FIELD_VALUE FROM CSAPP_PROTOCOL WHERE FIELD_NAME = 'LICENSEPLATE' AND PROTOCOL_PK = @protocolId)
	DECLARE @group VARCHAR(255) = (SELECT TOP 1 FIELD_VALUE FROM CSAPP_PROTOCOL WHERE FIELD_NAME = 'GROUP' AND PROTOCOL_PK = @protocolId)
	DECLARE @description VARCHAR(255) = (SELECT TOP 1 FIELD_VALUE FROM CSAPP_PROTOCOL WHERE FIELD_NAME = 'DESCRIPTION' AND PROTOCOL_PK = @protocolId)
	DECLARE @type VARCHAR(255) = (SELECT TOP 1 FIELD_VALUE FROM CSAPP_PROTOCOL WHERE FIELD_NAME = 'TYPE' AND PROTOCOL_PK = @protocolId)
	DECLARE @pict VARCHAR(255) = ISNULL((SELECT TOP 1 FIELD_VALUE FROM CSAPP_PROTOCOL WHERE FIELD_NAME = 'PICT' AND PROTOCOL_PK = @protocolId),
	(SELECT TOP 1 PK FROM AUTOS_GRP_SCHADEN_PIC WHERE Grp = @group))
	DECLARE @parkingArea VARCHAR(255) = (SELECT TOP 1 FIELD_VALUE FROM CSAPP_PROTOCOL WHERE FIELD_NAME = 'PARKPLATZ' AND PROTOCOL_PK = @protocolId)
	DECLARE @branch VARCHAR(255) = (SELECT TOP 1 FIELD_VALUE FROM CSAPP_PROTOCOL WHERE FIELD_NAME = 'STANDORT' AND PROTOCOL_PK = @protocolId)
	IF @mva IS NULL
		BEGIN
			EXEC dbo.CSAPP_KFZ_GET_NEXT_MVANUMBER @result = @mva OUTPUT -- varchar(10)
		END
	IF EXISTS(SELECT * FROM AUTOS WHERE MVANUMMER = @mva OR FAHRGESTELLNUMMER = @vin)
	BEGIN 
		UPDATE AUTOS SET FAHRGESTELLNUMMER = ISNULL(@vin, FAHRGESTELLNUMMER), MVANUMMER = ISNULL(@mva, MVANUMMER), [OWNER] = ISNULL(@owner, [OWNER]),
		LATITUDE = ISNULL(@latitude, LATITUDE), LONGITUDE = ISNULL(@longitude, LONGITUDE), KM = ISNULL(@km, KM), FUEL_LEVEL = ISNULL(@fuel, FUEL_LEVEL),
		KENNZEICHEN = ISNULL(@license, KENNZEICHEN), KFZGRUPPE = ISNULL(@group, KFZGRUPPE), FAHRZEUG = ISNULL(@description, FAHRZEUG), 
		KFZART = ISNULL(@type, KFZART), PK_PICT = ISNULL(@pict, PK_PICT), PARKPLATZ = ISNULL(@parkingArea, Parkplatz), Standort = ISNULL(@branch, Standort)
		WHERE  MVANUMMER = @mva OR FAHRGESTELLNUMMER = @vin
		SELECT @new = 0
	END
	ELSE
	BEGIN
		INSERT INTO dbo.AUTOS
		        ( MVANUMMER ,
		          KENNZEICHEN ,
		          FAHRZEUG ,
		          OWNER ,
		          KFZART ,
		          KFZGRUPPE ,
		          FAHRGESTELLNUMMER ,
		          ZULDAT ,
		          PK_PICT ,
		          Parkplatz ,
		          Standort ,
		          KM ,
		          FUEL_LEVEL ,
		          LONGITUDE ,
		          LATITUDE 
		        )
		VALUES  (
					@mva,
					@license,
					ISNULL(@description, 'APP'),
					ISNULL(@owner, 0),
					ISNULL(@type, 0),
					ISNULL(@group,'APP'),
					@vin,
					GETDATE(),
					@pict,
					@parkingArea,
					@branch,
					@km,
					@fuel,
					@longitude,
					@latitude
		        )  
		SELECT @new = 1
	END  
	EXEC dbo.aenderungseintrag_AUTOS @MVANummer = @mva, -- varchar(20)
	    @benutzer = 'API' -- varchar(30)
	IF NOT EXISTS (SELECT * FROM dbo.CSAPP_PROTOCOL WHERE PROTOCOL_PK = @protocolId AND FIELD_NAME = 'MVANUMBER')
	INSERT INTO dbo.CSAPP_PROTOCOL
	        ( PROTOCOL_PK ,
	          FIELD_NAME ,
	          FIELD_VALUE ,
	          VALUE_TYPE
	        )
	VALUES  ( @protocolId , -- PROTOCOL_PK - int
	          'MVANUMBER' , -- FIELD_NAME - varchar(100)
	          @mva , -- FIELD_VALUE - varchar(100)
	          'string'  -- VALUE_TYPE - varchar(255)
	        )
	
END
