CREATE OR ALTER PROCEDURE sp_StokFiyat_Bakim
AS
begin
	declare @i int,@sorgu nvarchar(max),@cnt nvarchar(2)
	set @i = 1;
    while @i <= 20 
	begin
	  SET @cnt = CAST(@i as varchar(2));
	  SET @sorgu = ' UPDATE StokFiyat SET  F'+@cnt+'FiyatH = CASE WHEN F'+@cnt+'Kdv = ''H'' THEN F'+@cnt+'Fiyat ELSE (F'+@cnt+'Fiyat/(100 + SatisKdv)*100) END';
	  --print @sorgu
	  EXEC sp_executesql @sorgu;
	  SET @i += 1;
    end;

	update sk SET sk.F1Fiyat = sf.F1Fiyat,
				  sk.F2Fiyat = sf.F2Fiyat,
				  sk.F3Fiyat = sf.F3Fiyat,
				  sk.F4Fiyat = sf.F4Fiyat
			 from STOKKARTI sk,StokFiyat sf where sk.Kodu = sf.Kodu
end;

go
CREATE OR ALTER PROCEDURE sp_StokKart_Bakim
AS
 
DELETE FROM StokVergiMuafiyetleri WHERE NOT EXISTS (SELECT * FROM STOKKARTI WHERE Kodu=StokVergiMuafiyetleri.StokKodu)
DELETE FROM StokFiyat  WHERE NOT EXISTS (SELECT * FROM STOKKARTI WHERE Kodu=StokFiyat.Kodu)
DELETE FROM StokBarkodlari WHERE NOT EXISTS (SELECT * FROM STOKKARTI WHERE Kodu=StokBarkodlari.StokKodu)
DELETE FROM StokDB WHERE NOT EXISTS (SELECT * FROM STOKKARTI WHERE Kodu=StokDB.StokKodu)
GO


create or alter proc sp_StokDoviz_Bakim 
as
update StokBarkodlari set AnaBirim=0 where AnaBirim is null or AnaBirim='' or AnaBirim<>1
UPDATE STOKHAR SET DovizKuru = (CASE WHEN DovizKodu in ('YP', 'TL') or SonTutar = 0 or SonTutarDov = 0 THEN 1 ELSE SonTutar / SonTutarDov END) 
UPDATE STOKHARDETBAG SET DovizKur = (CASE WHEN DovizKodu in ('YP', 'TL') or Tutar = 0 or DovizTut = 0 THEN 1 ELSE Tutar / DovizTut END)
UPDATE ACARI SET DovizKur = 1 where DOVIZKODU in ('YP', 'TL')
UPDATE KASA SET DovizKur = 1 where DovizKodu in ('YP', 'TL')
UPDATE ACARI SET DovizKur = (CASE WHEN BORC >0 AND DovizBorc > 0   THEN BORC / DovizBorc    WHEN ALACAK > 0 and DovizAlacak > 0 THEN ALACAK / DovizAlacak  ELSE 1 END)
UPDATE TEKLIFHAR SET DovizKur = (CASE WHEN DovizKodu in ('YP', 'TL') or SonTutar = 0 or SonTutarDov = 0 THEN 1 ELSE SonTutar / SonTutarDov END) 
UPDATE TEKLIFHARDETBAG SET DovizKur = (CASE WHEN DovizKodu in ('YP', 'TL') or Tutar = 0 or DovizTut = 0 THEN 1 ELSE Tutar / DovizTut END)
UPDATE SIPARISHAR SET DovizKur = (CASE WHEN DovizKodu in ('YP', 'TL') or SonTutar = 0 or SonTutarDov = 0 THEN 1 ELSE SonTutar / SonTutarDov END) 
UPDATE SIPARISHARDETBAG SET DovizKur = (CASE WHEN DovizKodu in ('YP', 'TL') or Tutar = 0 or DovizTut = 0 THEN 1 ELSE Tutar / DovizTut END)
UPDATE KASA SET DovizKur = (CASE WHEN BORC > 0 and DovizBorc > 0   THEN BORC / DovizBorc    WHEN ALACAK > 0 and DovizAlacak > 0 THEN ALACAK / DovizAlacak  ELSE 1 END)
select   DovizKodu,DovizKuru,max(FaturaTarihi) mxTarih, min(FaturaTarihi) mnTrh from STOKHAR group by DovizKodu,DovizKuru ORDER BY DovizKodu,DovizKuru 
select   DovizKodu,DovizKur,max(FaturaTarihi) mxTarih, min(FaturaTarihi) mnTrh from STOKHARDETBAG group by DovizKodu,DovizKur ORDER BY DovizKodu,DovizKur
select   DOVIZKODU,DovizKur,max(TARIH) mxTarih, min(TARIH) mnTrh from ACARI group by DOVIZKODU,DovizKur ORDER BY DOVIZKODU,DovizKur
select   DovizKodu,DovizKur,max(TARIH) mxTarih, min(TARIH) mnTrh from KASA group by DovizKodu,DovizKur ORDER BY DovizKodu,DovizKur

go

create or alter   procedure StokKodAdDegistir       
@STOKADI nvarchar(40),      
@STOKKODU nvarchar(30),      
@EskiStokKodu nvarchar(30),     
@STOKADI2 nvarchar(40) = ''  
as      
begin Transaction         
 UPDATE STOKHAR SET StokAdi = @STOKADI, StokKodu= @STOKKODU where StokKodu= @EskiStokKodu      
 UPDATE MuadilStoklar SET MuadilStokAdi = @STOKADI, MuadilStokKodu= @STOKKODU where MuadilStokKodu= @EskiStokKodu      
 UPDATE StokFiyat SET Kodu= @STOKKODU where Kodu= @EskiStokKodu      
 UPDATE StokDB SET StokKodu= @STOKKODU where StokKodu= @EskiStokKodu      
 UPDATE StokFiyatLog SET Kodu= @STOKKODU where Kodu= @EskiStokKodu      
 UPDATE StokMarkalari SET StokKodu= @STOKKODU where StokKodu=@EskiStokKodu      
 UPDATE Tedarikciler SET StokAdi = @STOKADI, StokKodu= @STOKKODU where StokKodu= @EskiStokKodu      
 UPDATE PYazarKasaStoklari SET StokAdi = @STOKADI, StokKodu= @STOKKODU where StokKodu= @EskiStokKodu      
 UPDATE PYazarKasaHar SET StokAdi = @STOKADI, StokKodu= @STOKKODU where StokKodu= @EskiStokKodu      
 UPDATE PPetrolHar SET StokAdi = @STOKADI, StokKodu= @STOKKODU where StokKodu= @EskiStokKodu      
 UPDATE PDegFiyatlar SET StokAdi = @STOKADI, StokKodu= @STOKKODU where StokKodu= @EskiStokKodu      
 UPDATE DonemselFiyatListesi SET StokKodu= @STOKKODU where StokKodu= @EskiStokKodu      
 UPDATE TekstilHareketTablosu SET StokKodu= @STOKKODU where StokKodu= @EskiStokKodu      
 UPDATE TekstilHataHareketTablosu SET StokKodu= @STOKKODU where StokKodu= @EskiStokKodu      
 UPDATE TekstilHataliStokVirmanSablonu SET HataliStokKodu= @STOKKODU where HataliStokKodu= @EskiStokKodu      
 UPDATE TekstilHataliStokVirmanSablonu SET VirmanStokKodu= @STOKKODU where VirmanStokKodu= @EskiStokKodu      
 UPDATE TekstilIsEmirleriDetayi SET StokKodu= @STOKKODU where StokKodu= @EskiStokKodu      
 UPDATE TekstilKesimTablosu SET StokKodu= @STOKKODU where StokKodu= @EskiStokKodu      
 UPDATE TekstilStokRenkTanimlari SET StokKodu= @STOKKODU where StokKodu= @EskiStokKodu      
 UPDATE CariUretimFiyatlari SET StokKodu= @STOKKODU where StokKodu= @EskiStokKodu      
 UPDATE Seri SET StokKodu= @STOKKODU where StokKodu= @EskiStokKodu      
 UPDATE Tarim_AmbalajStok SET StokKodu= @STOKKODU where StokKodu= @EskiStokKodu      
 UPDATE Tarim_AmbalajStok SET AmbalajKodu= @STOKKODU where AmbalajKodu= @EskiStokKodu      
 UPDATE StokDB SET StokKodu= @STOKKODU where StokKodu= @EskiStokKodu      
 update MH_Connect SET Degeri =@STOKKODU where Degeri=@EskiStokKodu and TabloAdi='STOKKARTI' and FieldAdi='Kodu'      
 Update StokTablolari   Set StokKodu=@STOKKODU Where StokKodu=@EskiStokKodu        
 Update Tarim_AmbalajStok  Set StokKodu=@STOKKODU Where StokKodu=@EskiStokKodu        
 Update Tarim_AmbalajStok  Set AmbalajKodu=@STOKKODU Where AmbalajKodu=@EskiStokKodu        
 Update Tarim_SatisEmirleri  Set StokKodu=@STOKKODU, StokAdi=@STOKADI Where StokKodu=@EskiStokKodu        
 Update Tarim_UretimDetay  Set StokKodu=@STOKKODU, StokAdi=@STOKADI Where StokKodu=@EskiStokKodu        
 Update Tarim_UretimDetay  Set KasaKodu=@STOKKODU Where KasaKodu=@EskiStokKodu        
 Update Tarim_UretimDetay  Set PaletKodu=@STOKKODU Where PaletKodu=@EskiStokKodu        
 Update Tarim_UretimDetay2  Set StokKodu=@STOKKODU, StokAdi=@STOKADI Where StokKodu=@EskiStokKodu        
 Update Tarim_UrunAlim   Set StokKodu=@STOKKODU, StokAdi=@STOKADI Where StokKodu=@EskiStokKodu        
 Update Tarim_UrunAlimAmbalaj Set StokKodu=@STOKKODU, StokAdi=@STOKADI Where StokKodu=@EskiStokKodu        
 Update Tarim_UrunAlimHareket Set KasaKodu=@STOKKODU Where KasaKodu=@EskiStokKodu        
 Update Tarim_UrunAlimHareket Set PaletKodu=@STOKKODU Where PaletKodu=@EskiStokKodu        
 Update StokTartim    Set StokKodu=@STOKKODU, StokAdi=@STOKADI Where StokKodu=@EskiStokKodu        
 Update StokTartimPalet   Set StokKodu=@STOKKODU, StokAdi=@STOKADI Where StokKodu=@EskiStokKodu       
 Update CariDonemFiyatlari set StokKodu=@STOKKODU Where StokKodu=@EskiStokKodu       
 Update ElTerminaliDepoSayim set StokKodu=@STOKKODU, StokAdi=@STOKADI  Where StokKodu=@EskiStokKodu       
 Update ElTerminaliYukleme set StokKodu=@STOKKODU, StokAdi=@STOKADI  Where StokKodu=@EskiStokKodu       
 Update EtiketBarkod set StokKodu=@STOKKODU, StokAdi=@STOKADI  Where StokKodu=@EskiStokKodu       
 Update MiktarsalHedefler set StokKodu=@STOKKODU Where StokKodu=@EskiStokKodu       
 Update RenkBedenSeriTablosu set StokKodu=@STOKKODU Where StokKodu=@EskiStokKodu       
 Update RenkBedenTablosu set StokKodu=@STOKKODU Where StokKodu=@EskiStokKodu       
 Update SIPARISHAR set StokKodu=@STOKKODU, StokAdi=@STOKADI  Where StokKodu=@EskiStokKodu       
 Update TEKLIFHAR set StokKodu=@STOKKODU, StokAdi=@STOKADI  Where StokKodu=@EskiStokKodu         
 Update SeriNoKaliteKontrol set StokKodu=@STOKKODU Where StokKodu=@EskiStokKodu       
 Update SipHar set StokKodu=@STOKKODU Where StokKodu=@EskiStokKodu     
 Update StokDepoMiktarlari set StokKodu=@STOKKODU Where StokKodu=@EskiStokKodu       
 Update StokVergiMuafiyetleri set StokKodu=@STOKKODU Where StokKodu=@EskiStokKodu       
 UPDATE InvoiceItemMap SET OurCode = @STOKKODU,SenderItemName = @STOKADI where OurCode = @EskiStokKodu      
 UPDATE STOKKARTI SET Kodu= @STOKKODU, StokAdi=@STOKADI,StokAdi2 = @STOKADI2 where Kodu= @EskiStokKodu   
 Update StokBarkodlari set StokKodu=@STOKKODU  Where StokKodu=@EskiStokKodu  
 Update StokBarkodlari set Barkodu=@STOKKODU  Where Barkodu=@EskiStokKodu       


commit; 

go

CREATE OR ALTER  PROCEDURE [dbo].[spStokFiyatDegistir]      
 @GelenStokKodu nvarchar(30),      
 @FiyatAdi nvarchar(30),      
 @Fiyat float,    
 @DovizKodu nvarchar(3)
as    
begin
	DECLARE @DovizKuru float
	DECLARE @Query nvarchar(max),@Fyt nvarchar(3)
    DECLARE @ResultDovizKodu nvarchar(3)

	IF LEN(@FiyatAdi) = 7
		SET @Fyt = SUBSTRING(@FiyatAdi,1,2)
    ELSE 
		SET @Fyt = SUBSTRING(@FiyatAdi,1,3);

	if @DovizKodu =''
	BEGIN
		SET @Query = 'select @DovizKod = '+@Fyt+'DovizKodu from StokFiyat where Kodu = '''+@GelenStokKodu+''''; 
		EXEC sp_executesql @Query,
						   N'@DovizKod nvarchar(3) OUTPUT',
						    @DovizKod = @ResultDovizKodu OUTPUT ;
	SET @DovizKodu =  @ResultDovizKodu;
	END;

	SET @DovizKuru = (Select GunFiyati from DOVIZT where DovizKodu = @DovizKodu)
	SET @Fiyat = @Fiyat / @DovizKuru;

    SET @Query = 'UPDATE StokFiyat SET '+@Fyt+'Fiyat = (CASE WHEN '+@Fyt+'Kdv = ''H'' THEN '+CAST(@Fiyat as nvarchar(50))+' ELSE ('+CAST(@Fiyat as nvarchar(50))+'*(100+SatisKdv))/100 END), '+@Fyt+'DovizKodu='''+@DovizKodu+''' where Kodu =''' + @GelenStokKodu + '''';
	EXEC sp_executesql @Query;	
	SET @Query = 'UPDATE StokFiyat SET  '+@Fyt+'FiyatH = (CASE WHEN '+@Fyt+'Kdv = ''D'' THEN '+CAST(@Fiyat as nvarchar(50))+' ELSE ('+CAST(@Fiyat as nvarchar(50))+'*(100+SatisKdv))/100 END) where Kodu = '''+@GelenStokKodu+'''';   
	EXEC sp_executesql @Query;	

	IF @Fyt in ('F1','F2','F3','F4')
	BEGIN
		/*SET @Query = 'UPDATE STOKKARTI SET '+@Fyt+'Fiyat = '+CAST(@Fiyat as nvarchar(50))+', '+@Fyt+'DovizKodu='''+@DovizKodu+''' where Kodu =''' + @GelenStokKodu+'''';
		EXEC sp_executesql @Query;	*/
	UPDATE sk SET sk.F1Fiyat	 = sf.F1Fiyat,
	              sk.F1DovizKodu = sf.F1DovizKodu,
				  sk.F2Fiyat	 = sf.F2Fiyat,
	              sk.F2DovizKodu = sf.F2DovizKodu,
				  sk.F3Fiyat	 = sf.F3Fiyat,
	              sk.F3DovizKodu = sf.F3DovizKodu,
				  sk.F4Fiyat	 = sf.F3Fiyat,
	              sk.F4DovizKodu = sf.F3DovizKodu
	         FROM  STOKKARTI sk,StokFiyat sf WHERE sk.Kodu = sf.Kodu and sk.Kodu = @GelenStokKodu
	END;
end;
 go
  
create or alter  proc  sp_stokbarkod_kaydet @Tip int,@StokNo int    
AS    
BEGIN    
--Tip 0 ise Stokkartı Giriş    
--Tip 1 ise StokBarkodları Giriş    
 DECLARE  @Kodu nvarchar(100), @Barkodu nvarchar(100),@AnaBirim nvarchar(20)  
     
 IF @Tip = 0    
 BEGIN    
  Select @Barkodu = Barkodu from STOKKARTI where StokNo = @StokNo;    
  
  IF ISNULL(@Barkodu,'') = ''  
  BEGIN    
 UPDATE STOKKARTI SET Barkodu = Kodu where StokNo = @StokNo;    
  END;  
  
  Select  @Kodu = Kodu,@Barkodu = Barkodu,@AnaBirim = AnaBirim from STOKKARTI where StokNo = @StokNo;    
     
  IF @Barkodu <> ''     
  BEGIN    
    IF (Select count(*) from StokBarkodlari Where StokNo = @StokNo and AnaBirim = 1) > 0    
    BEGIN    
  UPDATE StokBarkodlari SET Barkodu = @Barkodu,Birim = @AnaBirim where StokNo = @StokNo and AnaBirim = 1;    
    END    
    ELSE    
    BEGIN    
  insert into StokBarkodlari (StokKodu,Barkodu,KulNo,Carpani,UpdateDate,StokNo,Birim,AnaBirim)  
       values (@Kodu,@Barkodu,1,1,GETDATE(),@StokNo,@AnaBirim,1)      
    END;    
  END;     
 END    
 ELSE IF @Tip = 1    
 BEGIN    
  UPDATE sk     
     SET sk.Barkodu = sb.Barkodu,     
         sk.AnaBirim = sb.Birim    
    from STOKKARTI sk,    
         StokBarkodlari sb    
   where sk.StokNo = sb.StokNo    
     and sb.AnaBirim = 1    
     and sk.StokNo = @StokNo         
 END;    
END;    
    
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    
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='"';              
        
set @str='select count(*) from (select IslemNo from STOKHARDETBAG where FisTuru = ''F'' AND FaturaTarihi >='''+ @Tarih1+                    
''' and FaturaTarihi <= '''+@Tarih2+'''';                                      
                                 
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(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+')Y';                                                        

insert @tmp exec(@str)                                      
select @rc = trc from @tmp;             
              
declare @tstr varchar(100);                                    
                                    
if @orderby in (0,9)                                      
  set @tstr='IslemNo'                                      
if @orderby=1                                       
 set @tstr='FaturaNo'                                       
if @orderby=2                                       
 set @tstr='CariAdi'                                      
if @orderby=3                                       
 set @tstr='CariKodu'                                       
if @orderby=4                                      
 set @tstr='KULADI'                                      
if @orderby=5                                      
 set @tstr='SaticiAdi'                            
if @orderby=6                                      
 set @tstr='eFaturaDurumu'               
 if @orderby=7                                      
 set @tstr='YaziciCount'               
 if @orderby=8                                      
 set @tstr='FaturaTarihi'               
               
set @str='select '''' 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 IslemNo in (select X.IslemNo '+                                      
   'from (select ROW_NUMBER() over (order by '+@tstr+') as sira, IslemNo '+                                      
   'from STOKHARDETBAG where FisTuru = '''+@FaturaTuru+''' 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 = '''+@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(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+')X where X.sira >'+convert(varchar,@rc - @GosterilecekKayit) +') order by ';                                      
                                      
if @orderby in (0,9)                                      
  set @str=@str+'IslemNo'                  
if @orderby=1                                       
 set @str=@str+'FaturaNo'                                       
if @orderby=2                                       
 set @str=@str+'CariAdi'                                      
if @orderby=3                                       
 set @str=@str+'CariKodu'                        
if @orderby=4                                      
 set @str=@str+'KULADI'                                      
if @orderby=5                                      
 set @str=@str+'SaticiAdi'                                      
if @orderby=6                                      
 set @str=@str+'eFaturaDurumu'                 
if @orderby=7                                   
 set @str=@str+'YaziciCount'                 
if @orderby=8                                      
 set @str=@str+'FaturaTarihi'           

           
exec(@str)   
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    
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='"';              
        
set @str='select count(*) from (select IslemNo from STOKHARDETBAG where FisTuru = ''F'' AND FaturaTarihi >='''+ @Tarih1+                    
''' and FaturaTarihi <= '''+@Tarih2+'''';                                      
                                 
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(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+')Y';                                                        

insert @tmp exec(@str)                                      
select @rc = trc from @tmp;             
              
declare @tstr varchar(100);                                    
                                    
if @orderby in (0,9)                                      
  set @tstr='IslemNo'                                      
if @orderby=1                                       
 set @tstr='FaturaNo'                                       
if @orderby=2                                       
 set @tstr='CariAdi'                                      
if @orderby=3                                       
 set @tstr='CariKodu'                                       
if @orderby=4                                      
 set @tstr='KULADI'                                      
if @orderby=5                                      
 set @tstr='SaticiAdi'                            
if @orderby=6                                      
 set @tstr='eFaturaDurumu'               
 if @orderby=7                                      
 set @tstr='YaziciCount'               
 if @orderby=8                                      
 set @tstr='FaturaTarihi'               
               
set @str='select '''' 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 IslemNo in (select X.IslemNo '+                                      
   'from (select ROW_NUMBER() over (order by '+@tstr+') as sira, IslemNo '+                                      
   'from STOKHARDETBAG where FisTuru = '''+@FaturaTuru+''' 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 = '''+@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(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+')X where X.sira >'+convert(varchar,@rc - @GosterilecekKayit) +') order by ';                                      
                                      
if @orderby in (0,9)                                      
  set @str=@str+'IslemNo'                  
if @orderby=1                                       
 set @str=@str+'FaturaNo'                                       
if @orderby=2                                       
 set @str=@str+'CariAdi'                                      
if @orderby=3                                       
 set @str=@str+'CariKodu'                        
if @orderby=4                                      
 set @str=@str+'KULADI'                                      
if @orderby=5                                      
 set @str=@str+'SaticiAdi'                                      
if @orderby=6                                      
 set @str=@str+'eFaturaDurumu'                 
if @orderby=7                                   
 set @str=@str+'YaziciCount'                 
if @orderby=8                                      
 set @str=@str+'FaturaTarihi'           

           
exec(@str)   


