CREATE OR ALTER  PROCEDURE [dbo].[sp_FaturaIslemleriListesi]                                              
  @tarih1 varchar(20),                                               
  @tarih2 varchar(20),                                              
  @dep varchar(500),                                              
  @orderby int,                                              
  @arama varchar(100),                                              
  @GosterilecekKayit int,                                              
  @IslemTuru varchar(20),                                              
  @Durumu int,                                  
  @IslemTipi  varchar(30),                      
  @FatSerNo  varchar(3),                      
  @eFaturaNo  varchar(30),                      
  @FaturaTuru varchar(1),          
  @IslemNoLike bit = 0,            
  @TP varchar(2) = 'TF'
AS                                              
                  
declare @str nvarchar(max)                                              
declare @rc int                                               
declare @tmp table (trc int)                                              
declare @ayrac varchar(2)                      
declare @peFaturaNo varchar(50)              
if @eFaturaNo<>''              
select @peFaturaNo=UPPER(@eFaturaNo)              
else set @peFaturaNo=''              
set @ayrac='"';   
  
declare @tstr varchar(100);  
  
if @orderby in (0,8,9)                                              
  set @tstr='IslemNo DESC'                                              
if @orderby=1                                               
 set @tstr='FaturaNo DESC,IslemNo DESC'                                               
if @orderby=2                                               
 set @tstr='CariAdi DESC,IslemNo DESC'                                              
if @orderby=3                                               
 set @tstr='CariKodu DESC,IslemNo DESC'                                               
if @orderby=4                                              
 set @tstr='KULADI DESC,IslemNo DESC'                                              
if @orderby=5                                              
 set @tstr='SaticiAdi DESC,IslemNo DESC'                                    
if @orderby=6                                              
 set @tstr='eFaturaDurumu DESC,IslemNo DESC'                       
 if @orderby=7                                              
 set @tstr='YaziciCount DESC,IslemNo DESC'                       
                
set @str='select * from (select TOP ' + CAST(@GosterilecekKayit as nvarchar(10)) + ' ROW_NUMBER() over (order by '+@tstr+') as sira,'''' as plaka,*,'+  
            'CASE YazarKasaFisNo when 0 then ''TICARIFATURA'' '+                          
            'when 1 then ''TEMELFATURA'' '+                          
            'when 2 then ''IHRACAT'' else ''TICARIFATURA'' end + '' / ''+ case FaturaTipi  when 0 then ''SATIS'' when 1 then ''TEVKIFAT'' when 2 then ''IADE'' when 3 then ''IHRACKAYITLI'' when 4 then ''ISTISNA'' '+                           
            'when 5 then ''MOBIL'' else ''SATIS'' end FaturaSenaryoTip '+   
            'from STOKHARDETBAG where FisTuru = ''F'' AND TP = '''+ @TP + ''' AND FaturaTarihi >='''+ @Tarih1+ ''' and FaturaTarihi <= '''+@Tarih2+'''';           
      
if @peFaturaNo<>''                      
set @str=@str+'and upper(eFaturaNo) like upper(''%'+@peFaturaNo+'%'') '         
      
if @FatSerNo<>''                      
set @str=@str+'and Replace(FatSerNo,'''''''','''+@ayrac+''') = '''+@FatSerNo+''' '        
                                         
if @dep <> ''                                              
 set @str=@str+' and Departman in ('+@Dep+')'                                              
if @IslemTuru <> 'Tümü'                                              
 set @str=@str+' and IslemTuru = '''+@IslemTuru+''''                                    
if @IslemTipi <> 'Tümü' and   @IslemTipi <> ''                                             
 set @str=@str+' and IslemTipi like ''%'+@IslemTipi+'%'''                                    
          
if @arama <> ''           
begin          
if @orderby=0           
begin          
 if @IslemNoLike = 0          
 set @str=@str+'and CAST(IslemNo as nvarchar(20)) = '''+@arama+''' '           
 else          
 set @str=@str+'and IslemNo like ''%'+@arama+'%'' '           
end;                                            
if @orderby=1                                               
 set @str=@str+'and upper(FaturaNo) like upper('''+@arama+'%'') '                                               
if @orderby=2                                               
 set @str=@str+'and upper(CariAdi) like upper(''%'+@arama+'%'') '                                              
if @orderby=3                                               
 set @str=@str+'and upper(CariKodu) like upper('''+@arama+'%'') '                                               
if @orderby=4                                              
 set @str=@str+'and upper(KULADI) like upper(''%'+@arama+'%'') '                                              
if @orderby=5                                              
 set @str=@str+'and upper(SaticiAdi) like upper(''%'+@arama+'%'') '                                   
 if @orderby=6                                              
 set @str=@str+'and upper(eFaturaDurumu) like upper(''%'+@arama+'%'') '                                   
if @orderby=7                                              
 set @str=@str+'and upper(YaziciCount) like upper(''%'+@arama+'%'') '                                   
if @orderby=8                                              
 set @str=@str+'and upper(FaturaTarihi) like upper(''%'+@arama+'%'') '                 
if @orderby=9                                              
 set @str=@str+'and ((upper(Ack1) like upper(''%'+@arama+'%'')) or (upper(Ack2) like upper(''%'+@arama+'%'')) or (upper(Ack3) like upper(''%'+@arama+'%'')) or (upper(Ack4) like upper(''%'+@arama+'%''))         
   or EXISTS (Select 1 from ACARI AC where AC.ISLEMNO = IslemNo and upper(Aciklama2) like upper(''%'+@arama+'%''))        
      or EXISTS (Select 1 from ACIKLAMA AC where AC.FATURANO = IslemNo and upper(TeslimEden) like upper(''%'+@arama+'%''))        
      or EXISTS (Select 1 from ACIKLAMA AC where AC.FATURANO = IslemNo and upper(TeslimAlan) like upper(''%'+@arama+'%''))) '           
end;                                          
set @str=@str+')tbl ORDER BY IslemNo';                                                                
                                                               
--print @str  
execute sp_executesql  @str     