
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 r.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 WHERE pK = @protocolId
	UPDATE dbo.CSAPP_MAIN_PROTOCOL SET CI = CASE WHEN @coci = 2 THEN 1 ELSE 0 END WHERE pK = @protocolId
	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)
	ELSE
		UPDATE dbo.Unfallberichte SET Vertragsnummer = NULL WHERE PKSchaeden IN (SELECT PK FROM SCHAEDEN WHERE FILDO_ID = @protocolId)
		
	EXEC CSAPP_SEND_PROTOCOL @protocolId=@protocolId, @newCustomer=@newCustomer, @newCar=@newCar, @newDamage=@newDamage

END