declare @SQLString nvarchar(2000);
declare @name varchar(80);
declare @department varchar(80);
declare @vorname varchar(80);
declare @ort varchar(80);
declare @iata varchar(80);
declare @member varchar(80);
declare @remarks varchar(80);
declare @zip varchar(80);
declare @shortname varchar(80);
declare @email varchar(80);
declare @mcards varchar(80);
declare @mnummer varchar (100);
declare @mckdnr int; 
declare @kkdnr varchar(80);
declare @vip varchar(80);

set nocount on;
set @name='[&Last Name:;name1]%';
set @vorname='[&First Name:;firstname]%';
set @shortname='[&Search Name:;]%';
set @department='[&Department:]%';
set @ort='[&City:]%';
set @zip='[&ZIP Code:]%';
set @kkdnr= '[Profile No.:]';
set @iata='[&IATA:]%';
set @member='[&Hotel Member:;member]';
set @remarks='%[&Remarks:]%';
set @email='%[&eMail:]%';
set @mcards = '%[Member Card:]%';
set @mnummer = '%[MCard Nummer:]%';
set @vip='%[&VIP-Code:]%';


-- Liste der Memberkarten

 declare @mkdnr int, @mcardlistetxt varchar(250)
  set nocount on

if (object_id('tempdb..#mcardlistetxt') is null)
  create table #mcardlistetxt ("mkdnr" int, "mcardlistetxt" varchar(250))
else 
  delete from #mcardlistetxt

declare C cursor for 
select kunden.kdnr 
from kunden
inner join mcards on mcards.kdnr = kunden.kdnr
group by kunden.kdnr

open C
fetch next from C into @mkdnr
while (@@FETCH_STATUS = 0)
begin
  set @mcardlistetxt = '' 
  select 
    @mcardlistetxt = @mcardlistetxt + case when @mcardlistetxt <> '' then ', ' else '' end + isnull(mcdtypes.short, '')
from mcards
  inner join mcdtypes on (mcdtypes.ref = mcards.type) 
where mcards.kdnr = @mkdnr 
group by mcdtypes.short
  
 insert into #mcardlistetxt values (@mkdnr, @mcardlistetxt)
  fetch next from C into @mkdnr
end
close C
deallocate C

-- Liste der VIP Codes

declare @vkdnr int, @viplistetxt varchar(250)
  set nocount on

if (object_id('tempdb..#viplistetxt') is null)
  create table #viplistetxt ("vkdnr" int, "viplistetxt" varchar(250))
else 
  delete from #viplistetxt

declare C cursor for 
select kunden.kdnr 
from kunden
  left outer join mark2 on (kunden.kdnr = mark2.kundennr) and mark2.typ = 2
  left outer join vipcode on (vipcode.codenr = mark2.code) and mark2.typ = 2
  left outer join vipcode mainvip on mainvip.codenr = kunden.vip
group by kunden.kdnr

open C
fetch next from C into @vkdnr
while (@@FETCH_STATUS = 0)
begin
  set @viplistetxt = '' 
  select 
    @viplistetxt = @viplistetxt + case when @viplistetxt <> '' then ', ' else '' end + isnull(vipcode.bezeich, '')
from mark2
  inner join vipcode on (vipcode.codenr = mark2.code) and mark2.typ = 2
where mark2.kundennr = @vkdnr 
group by vipcode.bezeich
  
 insert into #viplistetxt values (@vkdnr, @viplistetxt)
  fetch next from C into @vkdnr
end
close C
deallocate C



SET @SQLString = N'
select kunden.kdnr,
name1+'' ''+name2+vorname as ''Name'',
shortname as ''Searchname'',
mastername+'' ''+accountname as ''Master/Account'',
isnull(vipcode.bezeich, '''') + '','' + isnull(viplistetxt, '''') as ''VIPCodes'', 
aufenth as ''NOS.'',
isnull(m.mcardlistetxt, '''') as ''Membercard'',
isnull(mcards1.value, '''') as ''MC-Number'',
ort as ''Ort'', kunden.kdnr as ''Profile No.'',
abteil as ''Department'',
strasse+'' ''+strasse2+'' ''+strasse3 as ''Addresse'',
resname+'' ''+resvorn+'' ''+resanr as ''Contact'',
gebdat as ''Date of birth'',
fibudeb as ''Accounting No.'',
bemerkung as ''Remarks'',
email as "eMail",
case typ
when 0 then ''Guest''
when 1 then ''Company''
when 2 then ''Group''
when 3 then ''Travel Agency''
when 4 then ''Source''
else ''invalid''
end as ''Typ'',
case when (numaccounts>0) then 85 else case when (numcontacts>0) then 86 else case when accountname!='''' then 87 else case when mastername!='''' then 86 else 80 end end end end as ''Icon''
from kunden
left outer join #viplistetxt v on v.vkdnr = kunden.kdnr
left outer join vipcode on vipcode.codenr = kunden.vip
left outer join (select kdnr, value from mcards) mcards1 on mcards1.kdnr = kunden.kdnr
left outer join #mcardlistetxt m on m.mkdnr = kunden.kdnr'

if (@vorname<>'%') SET @SQLString = @SQLString + N' where ((name1 like '''+@name+''' and vorname like '''+@vorname+''') or (rechname like '''+@name+''' and rechvorn like '''+@vorname+''') or (resname like '''+@name+''' and resvorn like '''+@vorname+''') or (shortname like '''+@name+''' and vorname like '''+@vorname+'''))';
else SET @SQLString = @SQLString + N' where ((name1 like '''+@name+''') or (rechname like '''+@name+''') or (resname like '''+@name+''') or (shortname like '''+@name+'''))';
if (@ort<>'%') SET @SQLString = @SQLString + N' and ort like '''+@ort+'''';
if (@iata<>'%') SET @SQLString = @SQLString + N' and iata like '''+@iata+'''';
if (@member<>'') SET @SQLString = @SQLString + N' and member='''+@member+'''';
if (@mcards <> '%%') SET @SQLString = @SQLString + N' and m.mcardlistetxt like '''+@mcards+'''';
if (@mnummer<>'%%') SET @SQLString = @SQLString + N' and mcards1.value like '''+@mnummer+'''';
if (@zip<>'%') SET @SQLString = @SQLString + N' and plz like '''+@zip+'''';
if (@department<>'%') SET @SQLString = @SQLString + N' and abteil like '''+@department+'''';
if (@remarks<>'%%') SET @SQLString = @SQLString + N' and bemerkung like '''+@remarks+'''';
if (@shortname<>'%') SET @SQLString = @SQLString + N' and shortname like '''+@shortname+'''';
if (@email<>'%') SET @SQLString = @SQLString + N' and email like '''+@email+'''';
if (@kkdnr<>'') SET @SQLString = @SQLString + N' and convert(varchar, (kunden.kdnr))='''+ convert(varchar,@kkdnr)+'''';
if (@vip<>'%%') SET @SQLString = @SQLString + N' and viplistetxt like '''+@vip+''' or vipcode.bezeich like '''+@vip+'''';

SET @SQLString=@SQLString+N' and deleted=0 order by name1,ort,abteil';
--print @SQLString
exec sp_executesql @SQLString