
CREATE  PROCEDURE dbo.CSAPP_EXTERN_AUTOS_MIT_MIETVERTAEGE_BY_BENUTZER_STATIONEN (@username varchar(255), @zugestellt VARCHAR(1) = NULL, @language VARCHAR(2) = 'DE', @limit INT = 0, @station VARCHAR(10) = '', @search VARCHAR(255))
AS SET NOCOUNT ON
DECLARE @mainQuery VARCHAR(MAX)
DECLARE @checkInContractQuery VARCHAR(MAX)
DECLARE @zustellungContractQuery VARCHAR(MAX)
DECLARE @unionQuery VARCHAR(MAX)
DECLARE @zugestelltQueryL VARCHAR(255)
DECLARE @zugestelltQueryR VARCHAR(255)
DECLARE @checkIn VARCHAR(20)
DECLARE @zustellung VARCHAR(20)
DECLARE @exchangedCarTranslation VARCHAR(255)
SET @exchangedCarTranslation =  (select dbo.LANG_GET_TEXT_BY_COMPONENT ('CheckInCheckOut.COCIAutoOverviewPickUpCarLabel',@language))
	  
DECLARE @firstTable TABLE ( aktion VARCHAR(20) NOT NULL, Kennzeichen VARCHAR(30) NOT NULL,  Fahrzeugnummer VARCHAR(30) NOT NULL,
						    FAHRZEUG VARCHAR(30) NOT NULL,
							bemerkung VARCHAR(MAX) NOT NULL,  VERTRAGSNUMMER VARCHAR(30) NOT NULL, 
							kunde VARCHAR(255) NOT NULL, zustellzeit DATETIME NOT NULL,  mietende DATETIME NOT NULL,
							endstation VARCHAR(20) NOT NULL, VERMIETSTATION VARCHAR(20) NOT NULL,
							vermittler INT,
							abholung_zustellung VARCHAR(100),
							gereinigt BIT,--, Zugestellt BIT,  AbhErl BIT
							vtc VARCHAR(15) NOT NULL
							)

SET @zustellung = '''ZUSTELLUNG'''
SET @checkIn = '''CHECKIN''' 
SET @zugestelltQueryL = 'AND ISDATE(m.Zustelldat)=1 AND (m.Zugestellt <> 1 OR m.Zugestellt IS NULL)'; -- = L
SET @zugestelltQueryR = 'AND m.AbhErl = 1'; -- = R
SET @zustellungContractQuery ='	  
      (SELECT '+@zustellung+' AS aktion, a.Kennzeichen, a.MVANUMMER AS  Fahrzeugnummer, a.FAHRZEUG,   ISNULL(SONDERKOND1, '' '') +'' ''+ dbo.PARSE_RTF_TO_TXT(m.Bemerkungen, DEFAULT) AS bemerkung,  
	  m.VERTRAGSNUMMER, k.NAME1 +'' ''+ k.NAME2 AS kunde,  CONVERT(datetime, dbo.Dattodt( m.abdat,m.abzeit), 111) AS zustellzeit, 
	  dbo.Dattodt(m.VEREINBRUECKGABEDAT,m.VEREINRUECKZEIT) AS mietende, 
	  endstation=CASE WHEN m.CODESTATAN IS NULL OR m.CODESTATAN = '''' THEN m.CODEVSTATAN  ELSE m.CODESTATAN END,  
	  m.VERMIETSTATION,
	  --m.Zustelldat, m.Abholdat,
	   --m.Zugestellt, m.AbhErl,
	  vermittler = CASE WHEN EXISTS(SELECT TOP 1 PK_KUNDEN  FROM dbo.VERM_REL_KUNDEN_MV WHERE  PK_MV = m.VERTRAGSNUMMER) THEN (SELECT TOP 1 PK_KUNDEN  FROM dbo.VERM_REL_KUNDEN_MV WHERE  PK_MV = m.VERTRAGSNUMMER) ELSE m.KUNDENNUMMER END, 
	  abholung_zustellung = ISNULL((select CASE WHEN dbo.VTR_GETVERMNAME(m1.vertragsnummer) IS NULL OR dbo.VTR_GETVERMNAME(m1.vertragsnummer) = '''' THEN k.name1 ELSE dbo.VTR_GETVERMNAME(m1.vertragsnummer) END FROM mietvertraege m1, kunden k  WHERE m1.kundennummer=k.nummer AND m1.vertragsnummer=dbo.VTR_GET_VORVERTRAG(m.vertragsnummer)  AND (m1.OFFENODERCHECKIN<>''I'' OR m1.offenodercheckin IS NULL) AND (m1.reservierung<>''J'' OR m1.Reservierung IS NULL) AND (m1.AbhErl=1) ), ''''),
	  ISNULL((SELECT GEREINIGT FROM dbo.AUTOS_REINIGUNG_BY_MV WHERE VERTRAGSNUMMER=m.VERTRAGSNUMMER),0) AS gereinigt,
	  m.vtc   
	  FROM dbo.MIETVERTRAEGE m, dbo.autos a, dbo.kunden k 
	  WHERE a.mvanummer=m.mvanummer AND k.nummer=m.kundennummer
	  --AND dbo.dattodt(m.abdat,m.abzeit)<=GETDATE() AND dbo.dattodt(m.VEREINBRUECKGABEDAt,m.VEREINRUECKZEIT)>=GETDATE()
	  AND (m.OFFENODERCHECKIN IS NULL OR m.OFFENODERCHECKIN<>''I'')
	  AND (m.reservierung<> ''J'' OR m.reservierung IS NULL)  
	  AND m.vermietstation IN (SELECT Station from dbo.Res_UserAllowedStation WHERE UserPK='''+@username+''' AND maychange= ''J'') 
	  '+@zugestelltQueryL+'   
	  )'
SET @checkInContractQuery = '
	  (SELECT '+@checkIn+' AS aktion, a.Kennzeichen, a.MVANUMMER AS  Fahrzeugnummer, a.FAHRZEUG,   ISNULL(SONDERKOND1, '' '') +'' ''+ dbo.PARSE_RTF_TO_TXT(m.Bemerkungen, DEFAULT) AS bemerkung, 
	  m.VERTRAGSNUMMER, k.NAME1 +'' ''+ k.NAME2 AS kunde,  CONVERT(datetime, dbo.Dattodt( m.abdat,m.abzeit), 111) AS zustellzeit, 
	  dbo.Dattodt(m.VEREINBRUECKGABEDAT,m.VEREINRUECKZEIT) AS mietende, 
	  endstation=CASE WHEN m.CODESTATAN IS NULL OR m.CODESTATAN = '''' THEN m.CODEVSTATAN  ELSE m.CODESTATAN END,  
	  m.VERMIETSTATION, 
	  --m.Zustelldat, m.Abholdat,
	  --m.Zugestellt, m.AbhErl,
	  vermittler = CASE WHEN EXISTS(SELECT TOP 1 PK_KUNDEN  FROM dbo.VERM_REL_KUNDEN_MV WHERE  PK_MV = m.VERTRAGSNUMMER) THEN (SELECT TOP 1 PK_KUNDEN  FROM dbo.VERM_REL_KUNDEN_MV WHERE  PK_MV = m.VERTRAGSNUMMER) ELSE m.KUNDENNUMMER END, 	  
	  abholung_zustellung = ISNULL((select CASE WHEN dbo.VTR_GETVERMNAME(m1.vertragsnummer) IS NULL OR dbo.VTR_GETVERMNAME(m1.vertragsnummer) = '''' THEN k.name1  ELSE dbo.VTR_GETVERMNAME(m1.vertragsnummer) END  FROM mietvertraege m1, kunden k  WHERE m1.kundennummer=k.nummer  AND m1.vertragsnummer=dbo.VTR_GET_NACHVERTRAG(m.vertragsnummer,'''')  AND (Reservierung<>''J'' OR reservierung IS NULL) AND (ISDATE(Zustelldat)=1) ), ''''),
	  ISNULL((SELECT GEREINIGT FROM dbo.AUTOS_REINIGUNG_BY_MV WHERE VERTRAGSNUMMER=m.VERTRAGSNUMMER),0) AS gereinigt,
	  m.vtc   
	  FROM dbo.MIETVERTRAEGE m, dbo.autos a, dbo.kunden k 
	  WHERE a.mvanummer=m.mvanummer AND k.nummer=m.kundennummer
	  -- AND dbo.dattodt(m.abdat,m.abzeit)<=GETDATE() AND dbo.dattodt(m.VEREINBRUECKGABEDAt,m.VEREINRUECKZEIT)>=GETDATE()
	  AND (m.OFFENODERCHECKIN IS NULL OR m.OFFENODERCHECKIN<>''I'')
	  AND (m.reservierung<> ''J'' OR m.reservierung IS NULL)  
	  AND m.vermietstation IN (SELECT Station from dbo.Res_UserAllowedStation WHERE UserPK='''+@username+''' AND maychange= ''J'')
	  '+@zugestelltQueryR+' 
	  )'
 
SET @unionQuery = ' UNION ALL '
SET @mainQuery = @zustellungContractQuery + @unionQuery + @checkInContractQuery+
          ' ORDER BY Kennzeichen DESC, aktion DESC '
--PRINT @mainQuery

 
  --if user wants just the zugestellted, then it is the normal query 
IF (@zugestellt = 1)
	BEGIN   
	  INSERT INTO @firstTable ---just a few entries 
	  EXEC (@mainQuery)
	  SELECT TOP (@limit) f.*, 
	  COALESCE (
	  		REPLACE(
					@exchangedCarTranslation , 
					'CAR_PLAQUE_REGISTRATION',
					(SELECT a.KENNZEICHEN FROM dbo.MIETVERTRAEGE m, autos a 
					WHERE a.mvanummer=m.mvanummer 
					AND m.KFZChangeGroupID=(SELECT kfzchangegroupid FROM dbo.MIETVERTRAEGE WHERE vertragsnummer=f.VERTRAGSNUMMER) 
					AND m.KFZExchangeNo=(SELECT KFZExchangeNo-1 FROM dbo.MIETVERTRAEGE WHERE vertragsnummer=f.VERTRAGSNUMMER) 
					)
				), 
				''	--COALESCE REPLACEMENT  
	  )  as ausgetauschtesAuto   
	  FROM  @firstTable f, autos a 
	  WHERE fahrzeugnummer = a.MVANUMMER 
	  AND((CONVERT (VARCHAR,vertragsnummer) LIKE '%'+@search+'%') OR
					(CONVERT (VARCHAR,a.FAHRGESTELLNUMMER) LIKE '%'+@search+'%') OR
					(CONVERT (VARCHAR,fahrzeugnummer) LIKE '%'+@search+'%') OR
					(CONVERT (VARCHAR,kunde) LIKE '%'+@search+'%') OR
					(REPLACE(REPLACE(a.KENNZEICHEN,'-',''),' ','') LIKE '%'+REPLACE(REPLACE(@search,'-',''),' ','')+'%'))
				
					AND (endstation LIKE '%' + @station + '%' OR VERMIETSTATION LIKE  '%' + @station + '%')
	END
ELSE 
	BEGIN   
	  DECLARE @secondTable TABLE (  aktion VARCHAR(20) NOT NULL, Kennzeichen VARCHAR(30) NOT NULL,  Fahrzeugnummer VARCHAR(30) NOT NULL,
									FAHRZEUG VARCHAR(30) NOT NULL,
									bemerkung VARCHAR(MAX) NOT NULL,  VERTRAGSNUMMER VARCHAR(30) NOT NULL, 
									kunde VARCHAR(255) NOT NULL, zustellzeit DATETIME NOT NULL,  mietende DATETIME NOT NULL,
									endstation VARCHAR(20) NOT NULL, VERMIETSTATION VARCHAR(20) NOT NULL, 
									vermittler INT,
									abholung_zustellung VARCHAR(100),
									gereinigt BIT,--, Zugestellt BIT,  AbhErl BIT
									vtc VARCHAR(15) NOT NULL
								 )
	  INSERT INTO @firstTable  --few entries
	  EXEC (@mainQuery) 
	  SET @mainQuery = (SELECT REPLACE(@mainQuery, @zustellungContractQuery,''));   --delete zustellung-query 
	  SET @mainQuery = (SELECT REPLACE(@mainQuery, @unionQuery,''));   -- union from query must be removed
	  SET @mainQuery = (SELECT REPLACE(@mainQuery, @zugestelltQueryR,''));   --delete from zugestellted query that condition

	  --PRINT @mainQuery
	  INSERT INTO @secondTable  --here are all entries
	  EXEC (@mainQuery)
	  SELECT TOP (@limit) s.*,
	  COALESCE (
	  		REPLACE(
					@exchangedCarTranslation , 
					'CAR_PLAQUE_REGISTRATION',
					(SELECT a.KENNZEICHEN FROM dbo.MIETVERTRAEGE m, autos a 
					WHERE a.mvanummer=m.mvanummer 
					AND m.KFZChangeGroupID=(SELECT kfzchangegroupid FROM dbo.MIETVERTRAEGE WHERE vertragsnummer=s.VERTRAGSNUMMER) 
					AND m.KFZExchangeNo=(SELECT KFZExchangeNo-1 FROM dbo.MIETVERTRAEGE WHERE vertragsnummer=s.VERTRAGSNUMMER) 
					)
				), 
				''	--COALESCE REPLACEMENT  
	  )  as ausgetauschtesAuto 
	  FROM @secondTable s, autos a 
	  WHERE fahrzeugnummer = a.MVANUMMER 
	  AND 
	   s.VERTRAGSNUMMER NOT IN (SELECT VERTRAGSNUMMER FROM @firstTable) -- get the difference between the two tables 	
	  AND 
   		((CONVERT (VARCHAR,vertragsnummer) LIKE '%'+@search+'%') OR
					(CONVERT (VARCHAR,a.FAHRGESTELLNUMMER) LIKE '%'+@search+'%') OR
					(CONVERT (VARCHAR,fahrzeugnummer) LIKE '%'+@search+'%') OR
					(CONVERT (VARCHAR,kunde) LIKE '%'+@search+'%') OR
					(REPLACE(REPLACE(a.KENNZEICHEN,'-',''),' ','') LIKE '%'+REPLACE(REPLACE(@search,'-',''),' ','')+'%'))
				
					AND (endstation LIKE '%' + @station + '%' OR VERMIETSTATION LIKE  '%' + @station + '%')
  	END