
CREATE OR ALTER   trigger [dbo].[STOKHAR_Degisim] on [dbo].[STOKHAR] for  DELETE ,UPDATE  
AS  
BEGIN  	
insert into [StokHarLog]   
Select  [NoId] ,[IslemNo] ,[IslemTuru] ,[CariNo] ,[StokNo] ,[StokKodu],[Miktar],[Birim],[BirimFiyat] ,  
  CONVERT(nvarchar(20) , [Iskonto1]) + ';' + CONVERT(nvarchar(20) , [Iskonto2]) + ';' + CONVERT(nvarchar(20) , [Iskonto3]) + ';' + CONVERT(nvarchar(20) , [Iskonto4]),  
  [SiparisNo],[SiparisTarihi],[Kdv] ,[FisTuru] ,[FaturaNo] ,[FaturaTarihi] ,[Satici] ,[DovizTut] ,[DovizKodu],[Depo] ,[KULNO],  
  CONVERT(nvarchar(20) , [AltIskYuzde1]) + ';' + CONVERT(nvarchar(20) , [AltIskYuzde2]) + ';' + CONVERT(nvarchar(20) , [AltIskYuzde3]),  
  [IslemNo3] , [Departman] , [SonTutar] , [SonTutarDov] , [SatMasrafTutari] , GETDATE()    
from deleted  where NOT EXISTS (select SayiAlan1 from TakasTablosu where KarekterAlan1 = 'RanrosSatisBekletme')
END;

go
-------------------------------------------------------------------------------------------------------------------------------------------  
CREATE  OR ALTER   PROCEDURE sp_Hareket_Bakim        
AS        
         
DELETE FROM STOKHAR WHERE Miktar in (NULL,'',' ',0)       
DELETE FROM SIPARISHAR WHERE Miktar   in (NULL,'',' ',0)       
DELETE FROM TEKLIFHAR WHERE Miktar in (NULL,'',' ',0)         
 ---DEPO VİRMANDAN GELEN İŞLEMNO OLMAYAN KAYITLAR İÇİN        
UPDATE STOKHAR SET IslemNo=IslemNo-1 where IslemNo in( select IslemNo from STOKHAR SH WHERE NOT EXISTS       
(SELECT * FROM STOKHARDETBAG SD WHERE SD.IslemNo=SH.IslemNo) AND IslemTuru='Depo Vir.')        
--STOKHAR OLUP STOKHARDETBAG OLMAYAN KAYITLARI GÖRMEK İÇİN        
select IslemNo from STOKHAR SH WHERE NOT EXISTS (SELECT * FROM STOKHARDETBAG SD WHERE SD.IslemNo=SH.IslemNo or SD.IslemNo=SH.IslemNo3)        
--STOKHAR OLUP STOKHARDETBAG OLMAYAN KAYITLARI SİLMEK İÇİN        
DELETE FROM STOKHAR WHERE IslemNo IN  (select IslemNo from STOKHAR SH WHERE NOT EXISTS       
(SELECT * FROM STOKHARDETBAG SD WHERE SD.IslemNo=SH.IslemNo or SD.IslemNo=SH.IslemNo3) AND IslemTuru<>'Depo Vir.' )   
  
-- Faturalaşmış Açık Kalan İrsaliyeleri Kapatan Sorgu  
UPDATE sb  SET AcikKapali = 'K' FROM STOKHARDETBAG sb  
 WHERE FisTuru = 'I'   
  and AcikKapali = 'A'   
  and FaturaNo > 0   
  and IslemTuru in ('Alış','Alış İade','Satış','Satış İade')  
  and EXISTS(select * from STOKHAR sh where sh.IslemNo = sb.IslemNo )  
go
CREATE OR ALTER PROCEDURE [dbo].[spBAKIM]  
        
AS  
 -- OlmayanKullanicininKullaniciTanimlariniSil  
  delete from KullaniciAyarlari where not EXISTS (select * from Kullanicilar where No = KullaniciNo)  
  UPDATE dbo.STOKKARTI SET AnaBirim = 'ADET' where AnaBirim IS NULL or AnaBirim = ''  
  UPDATE dbo.STOKKARTI SET IkinciBirimMiktari = 1   where IkinciBirimMiktari   IS NULL or IkinciBirimMiktari   = 0  
  UPDATE dbo.STOKKARTI SET UcuncuBirimMiktari = 1   where UcuncuBirimMiktari   IS NULL or UcuncuBirimMiktari   = 0  
  UPDATE dbo.STOKKARTI SET DorduncuBirimMiktari = 1 where DorduncuBirimMiktari IS NULL or DorduncuBirimMiktari = 0  
  UPDATE dbo.STOKKARTI SET BesinciBirimMiktari = 1  where BesinciBirimMiktari  IS NULL or BesinciBirimMiktari  = 0  
  UPDATE dbo.STOKKARTI SET AltinciBirimMiktari = 1  where AltinciBirimMiktari  IS NULL or AltinciBirimMiktari  = 0  
  UPDATE dbo.STOKKARTI SET StokEn = 0 where StokEn IS NULL  
  UPDATE dbo.STOKKARTI SET StokBoy = 0 where StokBoy IS NULL  
  UPDATE dbo.STOKKARTI SET StokYuksekligi = 0 where StokYuksekligi IS NULL  
  UPDATE dbo.STOKKARTI SET StokKilo = 0 where StokKilo IS NULL  
  UPDATE dbo.STOKKARTI SET OTVOrani = 0 where OTVOrani IS NULL  
  UPDATE dbo.STOKKARTI SET AlisIndirimYuzde   = 0 where AlisIndirimYuzde IS NULL  
  UPDATE dbo.STOKKARTI SET AlisIndirimYuzde2  = 0 where AlisIndirimYuzde2 IS NULL  
  UPDATE dbo.STOKKARTI SET AlisIndirimYuzde3  = 0 where AlisIndirimYuzde3 IS NULL  
  UPDATE dbo.STOKKARTI SET AlisIndirimYuzde4  = 0 where AlisIndirimYuzde4 IS NULL  
  UPDATE dbo.STOKKARTI SET SatisIndirimYuzde  = 0 where SatisIndirimYuzde IS NULL  
  UPDATE dbo.STOKKARTI SET SatisIndirimYuzde2 = 0 where SatisIndirimYuzde2 IS NULL  
  UPDATE dbo.STOKKARTI SET SatisIndirimYuzde3 = 0 where SatisIndirimYuzde3 IS NULL  
  UPDATE dbo.STOKKARTI SET SatisIndirimYuzde4 = 0 where SatisIndirimYuzde4 IS NULL  
  UPDATE dbo.STOKKARTI SET StokTipi = '+ Ürün' where StokTipi = 'Ürün'  
  UPDATE dbo.STOKKARTI SET F1Fiyat  = 0 where F1Fiyat IS NULL  
  UPDATE dbo.STOKKARTI SET F2Fiyat  = 0 where F2Fiyat IS NULL  
  UPDATE dbo.STOKKARTI SET F3Fiyat  = 0 where F3Fiyat IS NULL  
  UPDATE dbo.STOKKARTI SET F4Fiyat  = 0 where F4Fiyat IS NULL  
  UPDATE dbo.StokFiyat SET F1Fiyat  = 0 where F1Fiyat IS NULL  
  UPDATE dbo.StokFiyat SET F2Fiyat  = 0 where F2Fiyat IS NULL  
  UPDATE dbo.StokFiyat SET F3Fiyat  = 0 where F3Fiyat IS NULL  
  UPDATE dbo.StokFiyat SET F4Fiyat  = 0 where F4Fiyat IS NULL  
  UPDATE dbo.StokFiyat SET F5Fiyat  = 0 where F5Fiyat IS NULL  
  UPDATE dbo.StokFiyat SET F6Fiyat  = 0 where F6Fiyat IS NULL  
  UPDATE dbo.StokFiyat SET F7Fiyat  = 0 where F7Fiyat IS NULL  
  UPDATE dbo.StokFiyat SET F8Fiyat  = 0 where F8Fiyat IS NULL  
  UPDATE dbo.StokFiyat SET F9Fiyat  = 0 where F9Fiyat IS NULL  
  UPDATE dbo.StokFiyat SET F10Fiyat = 0 where F10Fiyat IS NULL  
  UPDATE dbo.StokFiyat SET F11Fiyat = 0 where F11Fiyat IS NULL  
  UPDATE dbo.StokFiyat SET F12Fiyat = 0 where F12Fiyat IS NULL  
  UPDATE dbo.StokFiyat SET F13Fiyat = 0 where F13Fiyat IS NULL  
  UPDATE dbo.StokFiyat SET F14Fiyat = 0 where F14Fiyat IS NULL  
  UPDATE dbo.StokFiyat SET F15Fiyat = 0 where F15Fiyat IS NULL  
  UPDATE dbo.StokFiyat SET F16Fiyat = 0 where F16Fiyat IS NULL  
  UPDATE dbo.StokFiyat SET F17Fiyat = 0 where F17Fiyat IS NULL  
  UPDATE dbo.StokFiyat SET F18Fiyat = 0 where F18Fiyat IS NULL  
  UPDATE dbo.StokFiyat SET F19Fiyat = 0 where F19Fiyat IS NULL  
  UPDATE dbo.StokFiyat SET F20Fiyat = 0 where F20Fiyat IS NULL  
  update STOKKARTI set IkinciBirim='' where IkinciBirim is NULL  
  update STOKKARTI set UcuncuBirim='' where UcuncuBirim is NULL  
  update STOKKARTI set DorduncuBirim='' where DorduncuBirim is NULL  
  update STOKKARTI set BesinciBirim='' where BesinciBirim is NULL  
  update STOKKARTI set AltinciBirim='' where AltinciBirim is NULL  
  --update STOKKARTI set Update_Date = GETDATE()  
  --update StokBarkodlari set UpdateDate = GETDATE()  
  Delete from dbo.StokBarkodlari Where StokKodu not in (Select Kodu from STOKKARTI)  
  Delete from dbo.StokFiyat Where Kodu not in (Select Kodu from STOKKARTI)  
  update AMUSTERI SET FiyatGrupNo  = (select NoId from GRUP where Turu='F' and GrupAdi='F.G.A.YOK') where FiyatGrupNo is null  
  update AMUSTERI SET FiyatGrupNo  = (select NoId from GRUP where Turu='F' and GrupAdi='F.G.A.YOK') where FiyatGrupNo =''  
  update AMUSTERI SET FiyatGrupAdi = (select GrupAdi from GRUP where NoId = FiyatGrupNo)  
  delete from GRUP where Turu <> 'F' and GrupAdi is null  
  delete from GRUP where Turu <> 'F' and GrupAdi = ''  
    
  delete from STOKHAR where IslemTuru = ''  
  delete from STOKHAR where CariKodu  = ''  
  delete from STOKHAR where CariKodu IS NULL  
  delete from STOKHAR where CariAdi = ''  
  delete from STOKHAR where StokKodu is null and StokAdi is null  
  delete from STOKHAR where StokKodu is null  
  delete from STOKHAR where Miktar = 0  
  delete from STOKHAR where Miktar IS NULL  
    
  update ACARI set TESSIRNO = '' where TESSIRNO is NULL  
  update Adres set Aciklama = '' where Aciklama is NULL  
  update AMUSTERI set FiyatGrupAdi = '' where FiyatGrupAdi is NULL  
  update AMUSTERI set FiyatGrupAdi = '' where FiyatGrupAdi is NULL  
  
delete from Adres where CariKodu=''  
update Adres SET Adres1 =(select MUSADRES1 from AMUSTERI where MUSKOD=CariKodu) where (Adres1='') or (Adres1 is null)  
update Adres SET Adres2 =(select MUSADRES2 from AMUSTERI where MUSKOD=CariKodu) where (Adres2='') or (Adres2 is null)  
update Adres SET Ilce =(select MUSILCE from AMUSTERI where MUSKOD=CariKodu) where (Ilce='') or (Ilce is null)  
update Adres SET Il =(select MUSIL from AMUSTERI where MUSKOD=CariKodu) where (Il='') or (Il is null)  
update Adres SET Ulke = 'TÜRKİYE' where (Ulke='') or (Ulke is null)  
update Adres SET TelUlkeKodu = '+90' where (TelUlkeKodu='') or (TelUlkeKodu is null)  
update Adres SET UlkeKodu = 'TR' where (UlkeKodu='') or (UlkeKodu is null)  
update Adres SET VergiDairesi =(select MUSVERGI from AMUSTERI where MUSKOD=CariKodu) where (VergiDairesi='') or (VergiDairesi is null)  
update Adres SET VergiNo =(select MUSVERNO from AMUSTERI where MUSKOD=CariKodu) where (VergiNo='') or (VergiNo is null)  
update Adres SET eFaturaMukellefimi = 'false' where (eFaturaMukellefimi='') or (eFaturaMukellefimi is null)  
update Adres SET eFaturaGuncellemeTarihi = getdate() where (eFaturaGuncellemeTarihi='') or (eFaturaGuncellemeTarihi is null)  
    
go
CREATE OR ALTER PROCEDURE sp_Bakim_Doviz_KdvDH_Update  
AS  
BEGIN  
 declare @DovizKod nvarchar(5),@KdvDH nvarchar(1)  
 declare @i int,@sorgu nvarchar(max),@cnt nvarchar(2)   
  
 IF (select count(*) from DOVIZT where DovizKodu = 'YP') > 0   
  SET @DovizKod = 'YP'  
 ELSE IF  (select count(*) from DOVIZT where DovizKodu = 'TL') > 0  
  SET @DovizKod = 'TL'  
 ELSE   
  select TOP 1  @DovizKod = DovizKodu from DOVIZT ORDER BY NoId   
         
 SET @i = 1;          
 WHILE @i <= 20           
 BEGIN          
    SET @cnt = CAST(@i as varchar(2));          
    SET @sorgu = ' UPDATE StokFiyat SET  F'+@cnt+'DovizKodu = ''' + @DovizKod + ''' WHERE  ISNULL(F'+@cnt+'DovizKodu,'''') = '''''                 
    EXEC sp_executesql @sorgu;     
    SET @i += 1;          
 END;          

 UPDATE StokFiyat SET F1Kdv = 'H' WHERE F1Kdv is null or F1Kdv not in ('D','H') 
 UPDATE StokFiyat SET F2Kdv = 'H' WHERE F2Kdv is null or F2Kdv not in ('D','H') 
 UPDATE StokFiyat SET F3Kdv = 'H' WHERE F3Kdv is null or F3Kdv not in ('D','H')  
 UPDATE StokFiyat SET F4Kdv = 'D' WHERE F4Kdv is null or F4Kdv not in ('D','H') 
   
 UPDATE sk SET sk.F1DovizKodu = sf.F1DovizKodu,     
    sk.F2DovizKodu = sf.F2DovizKodu,    
    sk.F3DovizKodu = sf.F3DovizKodu,    
    sk.F4DovizKodu = sf.F4DovizKodu,  
	sk.F1Kdv    = sf.F1Kdv,  
    sk.F2Kdv    = sf.F2Kdv,  
    sk.F3Kdv    = sf.F3Kdv, 
    sk.F4Kdv    = sf.F4Kdv
  FROM  STOKKARTI sk,StokFiyat sf WHERE sk.Kodu = sf.Kodu        
END;  
go
CREATE OR ALTER PROC sp_SN_Bakim          
AS      
BEGIN      
 ALTER TABLE STOKHAR DISABLE TRIGGER STOKHAR_Degisim
 UPDATE STOKHAR SET IslemTuru ='Mal Sayımı' where IslemTuru like '%Mal Say%'    
 UPDATE STOKHARDETBAG SET IslemTuru ='Mal Sayımı' where IslemTuru like '%Mal Say%'    
 EXEC spBAKIM  
 EXEC sp_Grup_Bakim            
 EXEC sp_StokDoviz_Bakim            
 EXEC sp_StokFiyat_Bakim            
 EXEC sp_StokFiyatBakim             
 EXEC sp_Kart_Bakim          
 EXEC sp_BAKIM_KOLON_UPDATE        
 EXEC sp_Hareket_Bakim      
 EXEC sp_BirimBarkod_Aktarim      
 EXEC sp_Bakim_Doviz_KdvDH_Update    
 exec sp_OdemePlaniKontrol  
 ALTER TABLE STOKHAR ENABLE TRIGGER STOKHAR_Degisim
END    
