IF NOT EXISTS(SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[CSAPP_PROTOCOL_SIGNATURES]'))
BEGIN
	CREATE TABLE [dbo].[CSAPP_PROTOCOL_SIGNATURES](
	    pk INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
	    PK_PROTOCOL int null,
	    signature_type varchar(255) NULL,
	    PK_DOCS int NULL
	   )


	INSERT INTO CSAPP_PROTOCOL_SIGNATURES (PK_PROTOCOL, signature_type, PK_DOCS)
	SELECT PROTOCOL_PK, FIELD_NAME, CASE  WHEN ISNUMERIC(FIELD_VALUE) = 1 THEN FIELD_VALUE ELSE NULL END FROM CSAPP_PROTOCOL 
	WHERE FIELD_NAME = 'SIGNATUREEMPLOYEE' OR  FIELD_NAME = 'SIGNATURECUSTOMER' OR FIELD_NAME = 'SIGNATURETHIRD'
END

IF NOT EXISTS(SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[CSAPP_MAIN_PROTOCOL]'))
BEGIN
	CREATE TABLE [dbo].[CSAPP_MAIN_PROTOCOL](
	    pk INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
	    CREATE_DATE DATETIME NULL,
		EDITOR VARCHAR(255) NULL,
		PROTOCOL_GROUP VARCHAR(255) NULL,
		LICENSE VARCHAR(255) NULL,
		VIN VARCHAR(255) NULL,
		GUID VARCHAR(255) NULL,
		PROTOCOL_TYPE INT NULL,
		PROTOCOL_KIND VARCHAR(255) NULL,
		IS_TEMPLATE BIT NULL,
		CO BIT NULL,
		CI BIT NULL
	   )
	SET IDENTITY_INSERT [dbo].[CSAPP_MAIN_PROTOCOL] ON 
	INSERT INTO CSAPP_MAIN_PROTOCOL (pk)
	SELECT DISTINCT PROTOCOL_PK FROM CSAPP_PROTOCOL
	SET IDENTITY_INSERT [dbo].[CSAPP_MAIN_PROTOCOL] OFF

	SET DATEFORMAT YMD
	UPDATE CSAPP_MAIN_PROTOCOL SET CREATE_DATE = (SELECT TOP 1  FIELD_VALUE FROM CSAPP_PROTOCOL WHERE FIELD_NAME = 'DATE' AND PROTOCOL_PK = CSAPP_MAIN_PROTOCOL.PK)
	UPDATE CSAPP_MAIN_PROTOCOL SET EDITOR = (SELECT TOP 1  FIELD_VALUE FROM CSAPP_PROTOCOL WHERE FIELD_NAME = 'EMPLOYEE' AND PROTOCOL_PK = CSAPP_MAIN_PROTOCOL.PK)
	UPDATE CSAPP_MAIN_PROTOCOL SET LICENSE = (SELECT TOP 1  FIELD_VALUE FROM CSAPP_PROTOCOL WHERE FIELD_NAME = 'LICENSE' AND PROTOCOL_PK = CSAPP_MAIN_PROTOCOL.PK)
	UPDATE CSAPP_MAIN_PROTOCOL SET VIN = (SELECT TOP 1 FIELD_VALUE FROM CSAPP_PROTOCOL WHERE FIELD_NAME = 'VIN' AND PROTOCOL_PK = CSAPP_MAIN_PROTOCOL.PK)

END

IF NOT EXISTS(SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[CSAPP_PROTOCOL]')
AND name = 'VALUE_TYPE')
BEGIN
	ALTER TABLE CSAPP_PROTOCOL ADD VALUE_TYPE VARCHAR(255) NULL
END
GO
IF NOT EXISTS(SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[rights]')
AND name = 'CSAPP_CCHEQ_PROTOCOL_GROUP')
	ALTER TABLE rights ADD CSAPP_CCHEQ_PROTOCOL_GROUP bit
GO

IF EXISTS (
       SELECT type_desc, type
       FROM sys.procedures WITH(NOLOCK)
       WHERE NAME = 'CSAPP_SEND_PROTOCOL'
           AND type = 'P'
     )
DROP PROCEDURE CSAPP_SEND_PROTOCOL
GO
CREATE PROCEDURE dbo.CSAPP_SEND_PROTOCOL @protocolId BIGINT, @newCustomer BIT = 0, @newCar BIT = 0, @newDamage BIT = 0
AS
BEGIN
	DECLARE @mailTransfer VARCHAR(255) = ISNULL((SELECT TOP 1  FIELD_VALUE FROM dbo.CSAPP_PROTOCOL WHERE PROTOCOL_PK = @protocolId AND FIELD_NAME = 'MAILTRANSFER'),'')
	DECLARE @mailAddresses VARCHAR(MAX) = ''

	IF @mailTransfer = '1'
	BEGIN
		SET @mailAddresses = @mailAddresses + ISNULL((SELECT TOP 1  FIELD_VALUE FROM dbo.CSAPP_PROTOCOL WHERE PROTOCOL_PK = @protocolId AND FIELD_NAME = 'MAIL'),'')
	END
	
	IF @newDamage = 1
	BEGIN
		SET @mailAddresses = @mailAddresses + CASE WHEN @mailAddresses <> '' THEN ',' END + (SELECT TOP 1  Wert FROM dbo.PARAMS WHERE Param  = 'SAPP_NEW_DAMAGE_MAIL')
	END
   
	IF @newCustomer = 1
	BEGIN
		SET @mailAddresses = @mailAddresses + CASE WHEN @mailAddresses <> '' THEN ',' END+ (SELECT TOP 1  Wert FROM dbo.PARAMS WHERE Param  = 'SAPP_NEWCUSTOMER_MAIL')
	END
	IF @newCar = 1
	BEGIN
		SET @mailAddresses = @mailAddresses + CASE WHEN @mailAddresses <> '' THEN ',' END+ (SELECT TOP 1  Wert FROM dbo.PARAMS WHERE Param  = 'SAPP_NEWVEHICLE_MAIL')
	END
	
	DECLARE @sendToStation BIT = (SELECT Wert FROM params WHERE param = 'BAPP_SEND_PROTOCOL_TO_STATION')
	DECLARE @station VARCHAR(30) =  ISNULL((SELECT TOP 1  FIELD_VALUE FROM dbo.CSAPP_PROTOCOL WHERE PROTOCOL_PK = @protocolId AND FIELD_NAME = 'STANDORT'),'')
	IF @sendToStation = 1
	BEGIN
		SET @mailAddresses = @mailAddresses + CASE WHEN @mailAddresses <> '' THEN ',' ELSE '' END + ISNULL((SELECT TOP 1  email FROM dbo.stammt WHERE station = @station),'')
	END  

	DECLARE @Object AS INT
	DECLARE @ResponseText AS VARCHAR(8000)	
	DECLARE @url varchar(255)
	DECLARE @reservationNumber VARCHAR(255) = ISNULL((SELECT TOP 1  FIELD_VALUE FROM dbo.CSAPP_PROTOCOL WHERE PROTOCOL_PK = @protocolId AND FIELD_NAME = 'RESERVATION'), '')
	DECLARE @mva VARCHAR(255) = ISNULL((SELECT TOP 1  FIELD_VALUE FROM dbo.CSAPP_PROTOCOL WHERE PROTOCOL_PK = @protocolId AND FIELD_NAME = 'MVANUMBER'), '')
	DECLARE @vin VARCHAR(255) = ISNULL((SELECT TOP 1  FIELD_VALUE FROM dbo.CSAPP_PROTOCOL WHERE PROTOCOL_PK = @protocolId AND FIELD_NAME = 'VIN'), '')
	DECLARE @license VARCHAR(255) = ISNULL((SELECT TOP 1  FIELD_VALUE FROM dbo.CSAPP_PROTOCOL WHERE PROTOCOL_PK = @protocolId AND FIELD_NAME = 'LICENSEPLATE'), '')
	DECLARE @parameters VARCHAR(MAX) = 'authcode=' + (SELECT Wert FROM PARAMS WHERE param  = 'SAPP_AUTHCODE') + '&protocol=' + CONVERT(VARCHAR(10), @protocolId)
	+ '&to=' + @mailAddresses + '&from=' + (SELECT Wert FROM PARAMS WHERE param  = 'SINET_MAIL_FROM')+'&reservationNumber='+@reservationNumber+'&mvaNumber=' + @mva + '&subject='+ (select dbo.LANG_GET_TEXT_BY_COMPONENT ('CSAPP_PROTOCOL_SUBJECT_DEFAULT','DE'))+'+' + @station 
	+ '+' + @vin + '+' + @license
	set @url  = (SELECT TOP 1 Wert FROM PARAMS WHERE PARAM = 'CSAPP_HOMEPAGE') + 'protocol'
					Exec sp_OACreate 'MSXML2.ServerXMLHTTP', @Object OUT;
					Exec sp_OAMethod @Object, 'open', NULL, 'post', @url,'false'
					Exec sp_OAMethod @Object, 'setRequestHeader', null, 'Content-Type', 'application/x-www-form-urlencoded'
					Exec sp_OAMethod @Object, 'send', NULL, @parameters
					Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
					Exec sp_OADestroy @Object
END


GO



IF EXISTS (
       SELECT type_desc, type
       FROM sys.procedures WITH(NOLOCK)
       WHERE NAME = 'CSAPP_UPDATE_CUSTOMER_FROM_PROTOCOL'
           AND type = 'P'
     )
DROP PROCEDURE CSAPP_UPDATE_CUSTOMER_FROM_PROTOCOL
GO
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
		INSERT INTO dbo.KUNDEN
		        ( NUMMER ,
		          NAME1 ,
		          STRASSE ,
		          PLZ ,
		          ORT ,
				  EMAIL
		        )
		VALUES  ( (SELECT pk FROM dbo.next_pks WHERE Tablename = 'KPK'), -- 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  
END

GO


IF EXISTS (
       SELECT type_desc, type
       FROM sys.procedures WITH(NOLOCK)
       WHERE NAME = 'CSAPP_UPDATE_CAR_FROM_PROTOCOL'
           AND type = 'P'
     )
DROP PROCEDURE CSAPP_UPDATE_CAR_FROM_PROTOCOL
GO
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 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,
					@description,
					ISNULL(@owner, 0),
					ISNULL(@type, 0),
					@group,
					@vin,
					GETDATE(),
					@pict,
					@parkingArea,
					@branch,
					@km,
					@fuel,
					@longitude,
					@latitude
		        )  
		SELECT @new = 1
	END  
END
GO

IF EXISTS (
       SELECT type_desc, type
       FROM sys.procedures WITH(NOLOCK)
       WHERE NAME = 'CSAPP_SET_PROTOCOL_DATA'
           AND type = 'P'
     )
DROP PROCEDURE CSAPP_SET_PROTOCOL_DATA
GO
CREATE PROCEDURE dbo.CSAPP_SET_PROTOCOL_DATA @protocolId BIGINT
AS
BEGIN

	SET DATEFORMAT YMD
	DECLARE @date DATETIME = CONVERT(DATETIME, (SELECT (SELECT  TOP 1 FIELD_VALUE FROM dbo.CSAPP_PROTOCOL WHERE PROTOCOL_PK = @protocolId AND FIELD_NAME = 'DATE')))
	UPDATE dbo.CSAPP_MAIN_PROTOCOL SET CREATE_DATE = @date WHERE PK = @protocolId
	SET LANGUAGE DEUTSCH

	DECLARE @employee VARCHAR(255) = (SELECT  TOP 1 FIELD_VALUE FROM dbo.CSAPP_PROTOCOL WHERE PROTOCOL_PK = @protocolId AND FIELD_NAME = 'EMPLOYEE') 
	UPDATE dbo.CSAPP_MAIN_PROTOCOL SET EDITOR = @employee WHERE PK = @protocolId
	
	DECLARE @protocolGroup VARCHAR(255) =( SELECT PK_GROUP FROM dbo.RIGHTS_USER_REL_GROUPS rrg , RIGHTS r  WHERE name = (SELECT Benutzer FROM dbo.MITARBEITER WHERE PK = @employee) AND rrg.PK_USER=r.name
AND rrg.PK_GROUP IN (SELECT name FROM Rights WHERE CSAPP_CCHEQ_PROTOCOL_GROUP=1)
)  
	UPDATE dbo.CSAPP_MAIN_PROTOCOL SET PROTOCOL_GROUP = @protocolGroup WHERE PK = @protocolId
	

	DECLARE @license VARCHAR(255) =  (SELECT TOP 1  FIELD_VALUE FROM dbo.CSAPP_PROTOCOL WHERE PROTOCOL_PK = @protocolId AND FIELD_NAME = 'LICENSEPLATE')
	UPDATE dbo.CSAPP_MAIN_PROTOCOL SET LICENSE = @license WHERE PK = @protocolId
	

	DECLARE @vin VARCHAR(255) = (SELECT TOP 1 FIELD_VALUE  FROM dbo.CSAPP_PROTOCOL WHERE PROTOCOL_PK = @protocolId AND FIELD_NAME = 'VIN')
	UPDATE dbo.CSAPP_MAIN_PROTOCOL SET VIN = @vin WHERE PK = @protocolId
  
    DECLARE @guid VARCHAR(255) = (SELECT TOP 1 FIELD_VALUE  FROM dbo.CSAPP_PROTOCOL WHERE PROTOCOL_PK = @protocolId AND FIELD_NAME = 'GUID')
	UPDATE dbo.CSAPP_MAIN_PROTOCOL SET [GUID] = @guid WHERE PK = @protocolId

	DECLARE @protocolType INT = convert(INT, (SELECT TOP 1 FIELD_VALUE  FROM dbo.CSAPP_PROTOCOL WHERE PROTOCOL_PK = @protocolId AND FIELD_NAME = 'PROTOCOLTYPE'))
	UPDATE dbo.CSAPP_MAIN_PROTOCOL SET PROTOCOL_TYPE = @protocolType WHERE PK = @protocolId

	DECLARE @protocolKind VARCHAR(255) = (SELECT TOP 1 FIELD_VALUE  FROM dbo.CSAPP_PROTOCOL WHERE PROTOCOL_PK = @protocolId AND FIELD_NAME = 'PROTOCOLKIND')
	UPDATE dbo.CSAPP_MAIN_PROTOCOL SET PROTOCOL_KIND = @protocolKind WHERE PK = @protocolId 

	DECLARE @isTemplate BIT = CONVERT(BIT, (SELECT TOP 1 FIELD_VALUE  FROM dbo.CSAPP_PROTOCOL WHERE PROTOCOL_PK = @protocolId AND FIELD_NAME = 'TEMPLATE'))
	UPDATE dbo.CSAPP_MAIN_PROTOCOL SET IS_TEMPLATE = @isTemplate WHERE PK = @protocolId

	DECLARE @coci INT = CONVERT(INT, (SELECT TOP 1 FIELD_VALUE  FROM dbo.CSAPP_PROTOCOL WHERE PROTOCOL_PK = @protocolId AND FIELD_NAME = 'STATUSACTION'))
	UPDATE dbo.CSAPP_MAIN_PROTOCOL SET CO = CASE WHEN @coci = 1 THEN 1 ELSE 0 END
	UPDATE dbo.CSAPP_MAIN_PROTOCOL SET CI = CASE WHEN @coci = 2 THEN 1 ELSE 0 END
	DECLARE @signatureEmployee INT = CONVERT(INT, (SELECT TOP 1 FIELD_VALUE  FROM dbo.CSAPP_PROTOCOL WHERE PROTOCOL_PK = @protocolId AND FIELD_NAME = 'SIGNATUREEMPLOYEE'))
	DECLARE @signatureCustomer INT = CONVERT(INT, (SELECT TOP 1 FIELD_VALUE  FROM dbo.CSAPP_PROTOCOL WHERE PROTOCOL_PK = @protocolId AND FIELD_NAME = 'SIGNATURECUSTOMER'))
	DECLARE @signatureThird INT = CONVERT(INT, (SELECT TOP 1 FIELD_VALUE  FROM dbo.CSAPP_PROTOCOL WHERE PROTOCOL_PK = @protocolId AND FIELD_NAME = 'SIGNATURETHIRD'))

	INSERT INTO dbo.CSAPP_PROTOCOL_SIGNATURES
	        ( PK_PROTOCOL ,
	          signature_type ,
	          PK_DOCS
	        )
	VALUES  ( @protocolId , -- PK_PROTOCOL - int
	          'SIGNATUREEMPLOYEE' , -- signature_type - varchar(255)
	          @signatureEmployee  -- PK_DOCS - int
	        ),
			 ( @protocolId , -- PK_PROTOCOL - int
	          'SIGNATURECUSTOMER' , -- signature_type - varchar(255)
	          @signatureCustomer  -- PK_DOCS - int
	        ),
			 ( @protocolId , -- PK_PROTOCOL - int
	          'SIGNATURETHIRD' , -- signature_type - varchar(255)
	          @signatureThird  -- PK_DOCS - int
	        )


	DECLARE @newCustomer BIT = 0
	DECLARE @newCar BIT = 0
	EXEC dbo.CSAPP_UPDATE_CAR_FROM_PROTOCOL @protocolId = @protocolId, @new = @newCar OUTPUT -- bigint
	EXEC dbo.CSAPP_UPDATE_CUSTOMER_FROM_PROTOCOL @protocolId = @protocolId, @new = @newCustomer OUTPUT -- bigint 

	DECLARE @editor varchar(255) = ISNULL((SELECT TOP 1 FIELD_VALUE  FROM dbo.CSAPP_PROTOCOL WHERE PROTOCOL_PK = @protocolId AND FIELD_NAME = 'EDITOR'), 0)
	DECLARE @mva varchar(255) = (SELECT TOP 1 FIELD_VALUE  FROM dbo.CSAPP_PROTOCOL WHERE PROTOCOL_PK = @protocolId AND FIELD_NAME = 'MVANUMBER')
	DECLARE @reservation varchar(255) = (SELECT TOP 1 FIELD_VALUE  FROM dbo.CSAPP_PROTOCOL WHERE PROTOCOL_PK = @protocolId AND FIELD_NAME = 'RESERVATION')
	DECLARE @branch varchar(255) = (SELECT TOP 1 FIELD_VALUE  FROM dbo.CSAPP_PROTOCOL WHERE PROTOCOL_PK = @protocolId AND FIELD_NAME = 'STANDORT')

	DECLARE @newDamage BIT = 0
	IF EXISTS (SELECT * FROM SCHAEDEN WHERE FILDO_ID = @protocolId)
		SELECT @newDamage = 1

	UPDATE dbo.SCHAEDEN 
	SET 
	Bearbeiter = ISNULL((SELECT TOP 1 printname FROM dbo.RIGHTS WHERE pk = @editor), 'api'),
	MVANUMMER = @mva,
	Vertragsnummer = CONVERT(INT, @reservation),
	Station = @branch
	WHERE FILDO_ID = @protocolId

	DECLARE @setReservationInAccidentReport bit = (SELECT saveDamageReport FROM dbo.CSAPP_PROTOCOL_TYPES WHERE PK = @protocolType)		

	IF (@setReservationInAccidentReport = 1)
		UPDATE dbo.Unfallberichte SET Vertragsnummer = @reservation WHERE PKSchaeden IN (SELECT PK FROM SCHAEDEN WHERE FILDO_ID = @protocolId)

	EXEC CSAPP_SEND_PROTOCOL @protocolId=@protocolId, @newCustomer=@newCustomer, @newCar=@newCar, @newDamage=@newDamage

END

GO



IF EXISTS (SELECT *
           FROM   sys.objects
           WHERE  object_id = OBJECT_ID(N'[dbo].[CSAPP_GET_USER_GROUP]')
                  AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
DROP FUNCTION CSAPP_GET_USER_GROUP
GO
CREATE FUNCTION CSAPP_GET_USER_GROUP(@name VARCHAR(255))
RETURNS VARCHAR(255)
AS
BEGIN

RETURN ( SELECT PK_GROUP FROM dbo.RIGHTS_USER_REL_GROUPS rrg , RIGHTS r  WHERE name = @name AND rrg.PK_USER=r.name
AND rrg.PK_GROUP IN (SELECT name FROM Rights WHERE CSAPP_CCHEQ_PROTOCOL_GROUP=1))
END

GO
