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,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'                             
 if @orderby=8                                                    
 set @tstr='FaturaTarihi DESC,IslemNo DESC'   
   
set @str='select * from (select TOP ' + CAST(@GosterilecekKayit as nvarchar(10)) + ' ROW_NUMBER() over (order by '+@tstr+') as sira,'''' as plaka,*'+           
            ' from STOKHARDETBAG where FisTuru = '''+ @FaturaTuru + ''' 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';                                                                      
   
  
if @orderby=0                                                     
 set @str=@str+')tbl ORDER BY IslemNo';                                                 
if @orderby=1                                                     
 set @str=@str+')tbl ORDER BY FaturaNo,IslemNo';                                                 
if @orderby=2                                                     
 set @str=@str+')tbl ORDER BY CariAdi ,IslemNo';     
if @orderby=3                                                     
 set @str=@str+')tbl ORDER BY CariKodu ,IslemNo';   
if @orderby=4                                                    
 set @str=@str+')tbl ORDER BY KULADI ,IslemNo';   
if @orderby=5                                                    
 set @str=@str+')tbl ORDER BY SaticiAdi,IslemNo';   
if @orderby=6      
 set @str=@str+')tbl ORDER BY eFaturaDurumu,IslemNo';   
if @orderby=7                                                    
 set @str=@str+')tbl ORDER BY YaziciCount,IslemNo';   
if @orderby=8                                                    
 set @str=@str+')tbl ORDER BY FaturaTarihi,IslemNo';   
if @orderby= 9                                                   
 set @str=@str+')tbl ORDER BY IslemNo';   
       
    
execute sp_executesql  @str