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






