
ALTER FUNCTION dbo.APP_CCHEQ_MV_MAIN_PRINTREPORT_FULLTANK (@PROTOCOL_PK BIGINT, @lang VARCHAR(3) = 'DE')  
RETURNS @ausdruck_input TABLE(AUSDRUCKBEZEICHNUNG VARCHAR(1024),VORGANGSNUMMER BIGINT NULL, MVANUMMER VARCHAR(32) NULL, STANDORT VARCHAR(512) NULL, KENNZEICHEN VARCHAR(512) NULL, KMSTAND INT NULL, FAHRZEUGTYP VARCHAR(1024) NULL,
TANKINHALT_RESERVE BIT NULL, TANKINHALT_1_8 BIT NULL, TANKINHALT_2_8 BIT NULL, TANKINHALT_3_8 BIT NULL, TANKINHALT_4_8 BIT NULL, TANKINHALT_5_8 BIT NULL, TANKINHALT_6_8 BIT NULL, TANKINHALT_7_8 BIT NULL, TANKINHALT_8_8 BIT NULL, 
VORSCHAEDEN BIT NULL,NAVIGATION_OHNE BIT NULL, NAVIGATION_STECKMODUL BIT NULL, NAVIGATION_COMAND BIT NULL, BEREIFUNG_WINTER BIT NULL, BEREIFUNG_SOMMER BIT NULL, 
BEREIFUNG_GANZJAHR BIT NULL, SIGNATURE_DATA VARCHAR(2048) NULL, CLIENT_NAME VARCHAR(512) NULL, CLIENT_SIGNATURE IMAGE NULL, EMPLOYEE_NAME VARCHAR(512) NULL, SIGNATUREEMPLOYEE IMAGE NULL, THIRD_NAME VARCHAR(512) NULL, SIGNATURETHIRD IMAGE NULL, CLIENT_STREET VARCHAR(512) NULL, CLIENT_TOWN VARCHAR(512) NULL, CLIENT_ZIPCODE VARCHAR(512) NULL, CLIENT_NUMBER BIGINT NULL, VIN VARCHAR(512) NULL, ENTERPRISE_LOGO IMAGE NULL, PARKPLATZ VARCHAR(512) NULL, ENTERPRISE_LOGO_BASE64 VARCHAR(MAX) NULL, REFERENZNUMMER VARCHAR(512) NULL, DATE VARCHAR(50)
)  
BEGIN  --Author José. Created on Mai 24th 2019 Aufgabe 1105961
	IF(@lang NOT IN('DE','US','NL','ES', 'RU')) SET @lang = 'DE'
	--SET @PROTOCOL_PK  = 1
	DECLARE @dateformat VARCHAR(32)= dbo.GET_DATE_FORMAT(@lang) 
	DECLARE @protocol_date VARCHAR(32) = ''
	DECLARE @AUSDRUCKBEZEICHNUNG VARCHAR(1024) =  'Kein Übergabeprotokoll', @VERTRAGSNUMMER BIGINT = 0, @MVANUMMER VARCHAR(32) = '', 
	@KUNDENNUMMER BIGINT = NULL, @OFFENODERCHECKIN VARCHAR(1) = '', @STANDORT VARCHAR(128)  = '', @KMSTAND BIGINT = 0, @TANK BIGINT = 0,
	@FAHRZEUGTYP VARCHAR(128) = '', @TANKINHALT_RESERVE BIT = 0, @TANKINHALT_1_8 BIT  = 0, @TANKINHALT_2_8 BIT = 0, 
	@TANKINHALT_3_8 BIT =  0, @TANKINHALT_4_8 BIT = 0, @TANKINHALT_5_8 BIT  = 0, @TANKINHALT_6_8 BIT = 0, 
	@TANKINHALT_7_8 BIT =  0, @TANKINHALT_8_8 BIT = 0, @VORSCHAEDEN BIT  = 0, @SIGNATURE_DATA VARCHAR(2048)  = 'Standort, '+dbo.getFormatDateFromString(CURRENT_TIMESTAMP, 'dd.mm.YYYY')+', ', --@CLIENT_SIGNATURE IMAGE = 0x89504e470d0a1a0a0000000d494844520000000a0000000a08060000008d32cfbd0000000473424954080808087c0864880000001849444154189563fcffffff7f06220013318a4615524f2100699a04108a8d0b680000000049454e44ae426082
    @SIGNATURECUSTOMER_DOC_ID BIGINT = NULL, @KENNZEICHEN VARCHAR(512) = '', @SIGNATURETHIRD_DOC_ID BIGINT = NULL, @SIGNATUREEMPLOYEE_DOC_ID BIGINT = NULL,
	@NAVIGATION_OHNE BIT = 0, @NAVIGATION_STECKMODUL BIT  = 0, @NAVIGATION_COMAND BIT  = 0, @BEREIFUNG_WINTER BIT  = 0, @BEREIFUNG_SOMMER BIT  = 0, 
	@BEREIFUNG_GANZJAHR BIT  = 0, @CLIENT_NAME VARCHAR(512) = '', @CLIENT_NUMBER BIGINT = NULL, @CLIENT_STREET VARCHAR(512) = '', @CLIENT_TOWN VARCHAR(512) = '', @CLIENT_ZIPCODE VARCHAR(512) = '', @EMPLOYEE_NAME VARCHAR(512) = 'Mitarbeiter', @THIRD_NAME VARCHAR(512) = '',
	@VIN VARCHAR(512) = '', @PARKPLATZ VARCHAR(512) = '', @REFERENZNUMMER VARCHAR(512) = NULL, @DATE VARCHAR(50) = NULL

	DECLARE @empty_img VARBINARY(MAX) = 0x89504e470d0a1a0a0000000d494844520000000a0000000a08060000008d32cfbd0000000473424954080808087c0864880000001849444154189563fcffffff7f06220013318a4615524f2100699a04108a8d0b680000000049454e44ae426082
	DECLARE @mandant_logo VARBINARY(MAX) = ISNULL((SELECT TOP 1 ISNULL(Logo,@empty_img) FROM dbo.MAND_GLOBAL),@empty_img)
	DECLARE @mandant_logo_base64  VARCHAR(MAX) 
    SET @mandant_logo_base64 =  cast('' as xml).value('xs:base64Binary(sql:variable("@mandant_logo"))', 'varchar(max)')

	--SET @AUSDRUCKBEZEICHNUNG = ISNULL((SELECT ISNULL(NAME,@AUSDRUCKBEZEICHNUNG) FROM CSAPP_PROTOCOL_TYPES WHERE PK = 1),@AUSDRUCKBEZEICHNUNG)	
	SET @AUSDRUCKBEZEICHNUNG = ISNULL((SELECT ISNULL(NAME,@AUSDRUCKBEZEICHNUNG) FROM CSAPP_PROTOCOL_TYPES WHERE PK = (select FIELD_VALUE FROM dbo.CSAPP_PROTOCOL WHERE PROTOCOL_PK = @PROTOCOL_PK AND FIELD_NAME = 'PROTOCOLTYPE')),@AUSDRUCKBEZEICHNUNG)	
	IF(@AUSDRUCKBEZEICHNUNG = 'Kein Übergabeprotokoll' OR dbo.IND_BERESA_CSAPP_PROTOKOLL(@PROTOCOL_PK) = 0)
		BEGIN
		    INSERT INTO @ausdruck_input 
				SELECT @AUSDRUCKBEZEICHNUNG,0,@MVANUMMER,'','',0,'',0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,' Minden '+ dbo.getFormatDateFromString(CURRENT_TIMESTAMP, 'dd.mm.YYYY')+' kein Kunden, ', '',@empty_img, '',@empty_img, '',@empty_img, @CLIENT_STREET, @CLIENT_TOWN, @CLIENT_ZIPCODE, @CLIENT_NUMBER, @VIN, @mandant_logo, @PARKPLATZ, @mandant_logo_base64, @REFERENZNUMMER, @DATE
			RETURN
		END
	---until this point there is a contract saved in the database 
		IF EXISTS (SELECT *  FROM dbo.CSAPP_PROTOCOL WHERE PROTOCOL_PK = @PROTOCOL_PK AND FIELD_NAME = 'RESERVATION' AND FIELD_VALUE <> '')
			BEGIN
				DECLARE @vtr VARCHAR(255) = (SELECT FIELD_VALUE  FROM dbo.CSAPP_PROTOCOL WHERE PROTOCOL_PK = @PROTOCOL_PK AND FIELD_NAME = 'RESERVATION')
				IF ISNUMERIC(@vtr) = 1
					SELECT @VERTRAGSNUMMER = CAST(ISNULL(@vtr,0) AS INT)				
			END
		ELSE
		IF EXISTS (SELECT *  FROM dbo.CSAPP_PROTOCOL WHERE PROTOCOL_PK = @PROTOCOL_PK AND FIELD_NAME = 'REFERENZ' AND FIELD_VALUE <> '')
			BEGIN
				DECLARE @reference VARCHAR(255)  
				SET @reference = (SELECT FIELD_VALUE  FROM dbo.CSAPP_PROTOCOL WHERE PROTOCOL_PK = @PROTOCOL_PK AND FIELD_NAME = 'REFERENZ' AND FIELD_VALUE <> '')       
				IF ISNUMERIC(@reference) = 1   
				BEGIN				       
					SELECT @VERTRAGSNUMMER = @reference
					SELECT @REFERENZNUMMER = @reference
				END
			END

		SELECT @MVANUMMER =  ISNULL(FIELD_VALUE,0) FROM dbo.CSAPP_PROTOCOL WHERE PROTOCOL_PK = @PROTOCOL_PK AND FIELD_NAME = 'MVANUMBER'
		SELECT @VIN =  ISNULL(FIELD_VALUE,0) FROM dbo.CSAPP_PROTOCOL WHERE PROTOCOL_PK = @PROTOCOL_PK AND FIELD_NAME = 'VIN'	
		SELECT @STANDORT = ISNULL(FIELD_VALUE,0) FROM dbo.CSAPP_PROTOCOL WHERE PROTOCOL_PK = @PROTOCOL_PK AND FIELD_NAME = 'STANDORT'

		IF (@MVANUMMER = 0)
			SELECT @MVANUMMER =  ISNULL((SELECT MVANUMMER FROM dbo.AUTOS WHERE FAHRGESTELLNUMMER = @VIN),'')
		
		IF (dbo.EXISTS_MVANUMMER(@MVANUMMER) = 1)
			BEGIN
			   -- SELECT @STANDORT =ISNULL((SELECT name FROM dbo.stammt WHERE station = ISNULL((SELECT Standort FROM dbo.AUTOS WHERE MVANUMMER = @MVANUMMER),'')),'')
			    SELECT @FAHRZEUGTYP =  ISNULL((SELECT FAHRZEUG FROM dbo.AUTOS WHERE MVANUMMER = @MVANUMMER),'')  
			    SELECT @KENNZEICHEN =  ISNULL((SELECT KENNZEICHEN FROM dbo.AUTOS WHERE MVANUMMER = @MVANUMMER),'')				
			    SELECT @VIN =  ISNULL((SELECT FAHRGESTELLNUMMER FROM dbo.AUTOS WHERE MVANUMMER = @MVANUMMER),'')				
			END
		
		IF EXISTS(SELECT * FROM CSAPP_PROTOCOL WHERE PROTOCOL_PK = @PROTOCOL_PK AND FIELD_NAME = 'KM')
			BEGIN	
				SELECT @KMSTAND = CAST(ISNULL(FIELD_VALUE,0) AS INT) FROM dbo.CSAPP_PROTOCOL WHERE PROTOCOL_PK = @PROTOCOL_PK AND FIELD_NAME = 'KM'
			END
		
		SELECT @TANK = CAST(ISNULL(FIELD_VALUE,0) AS INT) FROM dbo.CSAPP_PROTOCOL WHERE PROTOCOL_PK = @PROTOCOL_PK AND FIELD_NAME = 'TANK'
		SELECT @KUNDENNUMMER = CAST(ISNULL(FIELD_VALUE,0) AS INT) FROM dbo.CSAPP_PROTOCOL WHERE PROTOCOL_PK = @PROTOCOL_PK AND FIELD_NAME = 'CUSTOMER'
		SELECT @THIRD_NAME = ISNULL((SELECT FIELD_VALUE FROM dbo.CSAPP_PROTOCOL WHERE PROTOCOL_PK = @PROTOCOL_PK AND FIELD_NAME = 'ATTENDER'),'')
		--@CLIENT_NAME VARCHAR(512) = '', @CLIENT_STREET VARCHAR(512) = '', @CLIENT_TOWN VARCHAR(512) = '', @CLIENT_ZIPCODE
		SELECT @CLIENT_NAME = ISNULL((SELECT FIELD_VALUE FROM dbo.CSAPP_PROTOCOL WHERE PROTOCOL_PK = @PROTOCOL_PK AND FIELD_NAME = 'NAME'),'')
		SELECT @CLIENT_STREET = ISNULL((SELECT FIELD_VALUE FROM dbo.CSAPP_PROTOCOL WHERE PROTOCOL_PK = @PROTOCOL_PK AND FIELD_NAME = 'STREET'),'')
		SELECT @CLIENT_TOWN = ISNULL((SELECT FIELD_VALUE FROM dbo.CSAPP_PROTOCOL WHERE PROTOCOL_PK = @PROTOCOL_PK AND FIELD_NAME = 'TOWN'),'')
		SELECT @CLIENT_ZIPCODE = ISNULL((SELECT FIELD_VALUE FROM dbo.CSAPP_PROTOCOL WHERE PROTOCOL_PK = @PROTOCOL_PK AND FIELD_NAME = 'CODE'),'')
		SELECT @protocol_date = (ISNULL((SELECT FIELD_VALUE FROM dbo.CSAPP_PROTOCOL WHERE PROTOCOL_PK = @PROTOCOL_PK AND FIELD_NAME = 'DATE'),''))
		SELECT @PARKPLATZ = (ISNULL((SELECT FIELD_VALUE FROM dbo.CSAPP_PROTOCOL WHERE PROTOCOL_PK = @PROTOCOL_PK AND FIELD_NAME = 'PARKPLATZ'),''))
		SELECT @DATE = (ISNULL((SELECT FIELD_VALUE FROM dbo.CSAPP_PROTOCOL WHERE PROTOCOL_PK = @PROTOCOL_PK AND FIELD_NAME = 'DATE'),''))
			
		IF(ISDATE(@protocol_date)=1)
			SET @protocol_date = CONVERT(VARCHAR(10), CONVERT(date, @protocol_date), 104);
		IF (ISDATE(@protocol_date)=0)
			BEGIN
				SET @protocol_date = REPLACE(LTRIM(RTRIM(@protocol_date)),' ','T')
				IF(ISDATE(@protocol_date)=1)
					SET @protocol_date = CONVERT(VARCHAR(10), CONVERT(date, @protocol_date), 104);
				ELSE 
					SET @protocol_date = CONVERT(VARCHAR(10), CONVERT(date, GETDATE()), 104);
					--SET @protocol_date = @protocol_date-- dbo.getFormatDateFromString(CURRENT_TIMESTAMP, 'dd.mm.YYYY')
			END

	    IF(ISDATE(@DATE)=1)
			SET @DATE = CONVERT(VARCHAR(10), CONVERT(date, @DATE), 104)+' '+CONVERT(VARCHAR(10), CONVERT(datetime, @DATE), 108);
		IF (ISDATE(@DATE)=0)
			BEGIN
				SET @DATE = REPLACE(LTRIM(RTRIM(@DATE)),' ','T')
				IF(ISDATE(@DATE)=1)
					SET @DATE = CONVERT(VARCHAR(10), CONVERT(date, @DATE), 104)+' '+CONVERT(VARCHAR(5), CONVERT(datetime, @DATE), 108);
				ELSE 
					SET @DATE = CONVERT(VARCHAR(10), CONVERT(date, GETDATE()), 104)+' '+CONVERT(VARCHAR(5), CONVERT(datetime, @DATE), 108);
			END
			 

		SELECT @SIGNATURE_DATA = ISNULL((SELECT FIELD_VALUE FROM dbo.CSAPP_PROTOCOL WHERE PROTOCOL_PK = @PROTOCOL_PK AND FIELD_NAME = 'STANDORT'),'Standort')+', '+@protocol_date+', '
		IF(@CLIENT_ZIPCODE <> '')
			SET @CLIENT_TOWN = @CLIENT_ZIPCODE+ ', ' +@CLIENT_TOWN
		DECLARE @EMPLOYEE_NUMBER INT
		SELECT @EMPLOYEE_NUMBER = FIELD_VALUE FROM dbo.CSAPP_PROTOCOL WHERE PROTOCOL_PK = @PROTOCOL_PK AND FIELD_NAME = 'EMPLOYEE'
	    SELECT @SIGNATURE_DATA  = ISNULL((SELECT ort FROM dbo.stammt WHERE station = @STANDORT),'')+', '+@protocol_date+', '
		
		IF(@TANK IN (0)) SET @TANKINHALT_RESERVE = 1
		IF(@TANK IN (1)) SET @TANKINHALT_1_8 = 1
		IF(@TANK IN (2)) SET @TANKINHALT_2_8 = 1
		IF(@TANK IN (3)) SET @TANKINHALT_3_8 = 1
		IF(@TANK IN (4)) SET @TANKINHALT_4_8 = 1
		IF(@TANK IN (5)) SET @TANKINHALT_5_8 = 1
		IF(@TANK IN (6)) SET @TANKINHALT_6_8 = 1
		IF(@TANK IN (7)) SET @TANKINHALT_7_8 = 1
		IF(@TANK >= 8)   SET @TANKINHALT_8_8 = 1

		SET @BEREIFUNG_WINTER = 1
		SET @NAVIGATION_COMAND = 1
		--SET  @SIGNATURE_DATA += 
		--SET @CLIENT_NAME = SUBSTRING(dbo.VTR_GET_KUNDENNAME(@KUNDENNUMMER),1,50) 
		SET @EMPLOYEE_NAME = ISNULL((SELECT [Name] FROM dbo.MITARBEITER WHERE PK = CAST(@EMPLOYEE_NUMBER AS INT)),'')
		IF LTRIM(RTRIM(@EMPLOYEE_NAME)) = ''
		BEGIN
			SET @EMPLOYEE_NAME = ISNULL((SELECT Benutzer FROM dbo.MITARBEITER WHERE PK = CAST(@EMPLOYEE_NUMBER AS INT)),'') 
		END	

		IF(ISNUMERIC((SELECT FIELD_VALUE FROM dbo.CSAPP_PROTOCOL WHERE PROTOCOL_PK = @PROTOCOL_PK AND FIELD_NAME = 'SIGNATURECUSTOMER'))=1)
			SELECT @SIGNATURECUSTOMER_DOC_ID = CAST(FIELD_VALUE AS BIGINT) FROM dbo.CSAPP_PROTOCOL WHERE PROTOCOL_PK = @PROTOCOL_PK AND FIELD_NAME = 'SIGNATURECUSTOMER'
		IF(ISNUMERIC((SELECT FIELD_VALUE FROM dbo.CSAPP_PROTOCOL WHERE PROTOCOL_PK = @PROTOCOL_PK AND FIELD_NAME = 'SIGNATUREEMPLOYEE'))=1)
			SELECT @SIGNATUREEMPLOYEE_DOC_ID =  CAST(FIELD_VALUE AS BIGINT) FROM dbo.CSAPP_PROTOCOL WHERE PROTOCOL_PK = @PROTOCOL_PK AND FIELD_NAME = 'SIGNATUREEMPLOYEE'
		IF(ISNUMERIC((SELECT FIELD_VALUE FROM dbo.CSAPP_PROTOCOL WHERE PROTOCOL_PK = @PROTOCOL_PK AND FIELD_NAME = 'SIGNATURETHIRD'))=1)
			SELECT @SIGNATURETHIRD_DOC_ID =  CAST(FIELD_VALUE AS BIGINT) FROM dbo.CSAPP_PROTOCOL WHERE PROTOCOL_PK = @PROTOCOL_PK AND FIELD_NAME = 'SIGNATURETHIRD'		
		INSERT INTO @ausdruck_input 
			SELECT @AUSDRUCKBEZEICHNUNG,@VERTRAGSNUMMER,@MVANUMMER,@STANDORT,@KENNZEICHEN,@KMSTAND,@FAHRZEUGTYP,@TANKINHALT_RESERVE, @TANKINHALT_1_8, @TANKINHALT_2_8, @TANKINHALT_3_8, @TANKINHALT_4_8, @TANKINHALT_5_8, @TANKINHALT_6_8, @TANKINHALT_7_8, @TANKINHALT_8_8, @VORSCHAEDEN, @NAVIGATION_OHNE, @NAVIGATION_STECKMODUL, @NAVIGATION_COMAND, @BEREIFUNG_WINTER, @BEREIFUNG_SOMMER, @BEREIFUNG_GANZJAHR, @SIGNATURE_DATA, @CLIENT_NAME, @empty_img, @EMPLOYEE_NAME, @empty_img, @THIRD_NAME, @empty_img, @CLIENT_STREET, @CLIENT_TOWN, @CLIENT_ZIPCODE, @KUNDENNUMMER, @VIN, @mandant_logo, @PARKPLATZ, @mandant_logo_base64, @REFERENZNUMMER, @DATE
		IF ISNUMERIC(@SIGNATURECUSTOMER_DOC_ID) = 1 AND ISNULL((SELECT TOP 1 DATALENGTH(Beleg)  FROM dbo.DOCS_DOCS WHERE PK = @SIGNATURECUSTOMER_DOC_ID),0) > 0
			BEGIN
			    UPDATE @ausdruck_input SET CLIENT_SIGNATURE = (SELECT TOP 1 Beleg  FROM dbo.DOCS_DOCS WHERE PK = @SIGNATURECUSTOMER_DOC_ID) 
			END
		IF ISNUMERIC(@SIGNATUREEMPLOYEE_DOC_ID) = 1 AND ISNULL((SELECT TOP 1 DATALENGTH(Beleg)  FROM dbo.DOCS_DOCS WHERE PK = @SIGNATUREEMPLOYEE_DOC_ID),0) > 0
			BEGIN
			    UPDATE @ausdruck_input SET SIGNATUREEMPLOYEE = (SELECT TOP 1 Beleg  FROM dbo.DOCS_DOCS WHERE PK = @SIGNATUREEMPLOYEE_DOC_ID) 
			END
		IF ISNUMERIC(@SIGNATURETHIRD_DOC_ID) = 1 AND ISNULL((SELECT TOP 1 DATALENGTH(Beleg)  FROM dbo.DOCS_DOCS WHERE PK = @SIGNATURETHIRD_DOC_ID),0) > 0
			BEGIN
			    UPDATE @ausdruck_input SET SIGNATURETHIRD = (SELECT TOP 1 Beleg  FROM dbo.DOCS_DOCS WHERE PK = @SIGNATURETHIRD_DOC_ID) 
			END
			
		RETURN 
		
  RETURN
END