
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
