DELETE GridView WHERE KayitIsmi = 'FrmFaturaIslemlericxGrid1cxGrid1DBTableView1'
GO
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,*'+         
            ' 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 