--Firma Bilgi düzeltmesi
ALTER TABLE FIRMABILGI ADD NAKITYUZDE INTEGER DEFAULT 0;
update FIRMABILGI set NAKITYUZDE=0;
update firmabilgi set firmabilgi.yil=2024;

-- Satış Tabloları Alan düzeltmesi
ALTER TABLE SBASLIK ALTER COLUMN SATALAN1 SET DEFAULT '0';
ALTER TABLE SBASLIK ADD SATGENELTOPN FLOAT DEFAULT 0;

-- Ürünler K.K Satış fıyatı alan ekleme;
ALTER TABLE URUNLER ADD USONSATFIYN FLOAT;
update urunler set USONSATFIYN=USONSATFIY;

-- Hızlı ekran ürün adı düzeltmesi
create or alter procedure HIZLI_DUZLET
as
begin
  merge into hizli s
  using urunler u
  on s.hurid =u.uid
  when matched then
  update set
  s.huad =u.uad,
  s.hubarkod=u.ubarkod;
  suspend;
end;

-- Cari düzeltmesi
CREATE OR ALTER trigger cari_temizle for cari
active after insert or update position 0
AS
begin
  delete from cari where cfirmaadi is null or cfirmaadi ='';
end;

-- OBRK2 düzeltmesi

CREATE OR ALTER procedure OBRK2_DUZELT
as
begin
  update urunler set obrk2=replace(obrk2,ASCII_CHAR(13)||ASCII_CHAR(10)||ASCII_CHAR(13)||ASCII_CHAR(10),ASCII_CHAR(13)||ASCII_CHAR(10))
where obrk2 like '%' ||ASCII_CHAR(13)||ASCII_CHAR(10)||'%';
  suspend;
end;


-- Silinen ekle düzeltmesi
CREATE OR ALTER trigger silinen_ekle for sfatdetay
active before delete position 0
as
begin
  insert into SATIS_SILINEN (SSTARIH, SSSAAT, SSKASIYER, SSBARKOD, SSUAD, SSFIYAT, SSADET,SSSATNO)
  select current_date, current_time,SFATKUL, SFATURUNBARKOD, SFATURUNAD, SFATTUTAR, SFATMIKTAR, sfatbasid
                                       from SFATDETAY where sfatid = old.sfatid;
  Delete From satis_silinen where sstarih<= current_date-30;
end;


CREATE OR ALTER procedure NAKIT_HESAPLA (
    STID bigint not null,
    NAKIT_IND integer not null)
as
begin
  if (nakit_ind>0) then
  begin
  update sfatdetay set
  SFATTUTAR=round(SFATTUTAR*(100-:nakit_ind)/100),
  SFATARATOPLAM=round(SFATARATOPLAM*(100-:nakit_ind)/100),
  SFATTOPLAM=round(SFATTOPLAM*(100-:nakit_ind)/100),
  SFATANATUTAR=round(SFATANATUTAR*(100-:nakit_ind)/100)
  where SFATBASID=:STID;

  update sbaslik set
  SATTOPLAM=round(SATTOPLAM*(100-:nakit_ind)/100),
  SATISKTOPLAM=round(SATISKTOPLAM*(100-:nakit_ind)/100),
  SATARATOPLAM=round(SATARATOPLAM*(100-:nakit_ind)/100),
  SATGENELTOP=round(SATGENELTOP*(100-:nakit_ind)/100),
  SATGENELTOPN=round(SATGENELTOP*(100-:nakit_ind)/100)
  where SATID=:STID;
  end
  suspend;
end;

CREATE OR ALTER procedure FIYAT_DUZELT (
    URID bigint)
as
declare variable YUZDE integer;
begin
 YUZDE = (Select first 1 NAKITYUZDE from firmabilgi);
 if (:YUZDE <> 0) then
 update urunler set usonsatfiyn=round(usonsatfiy*(100-:YUZDE)/100) where uid=:urid;
 else
 update urunler set usonsatfiyn=usonsatfiy where uid=:urid;
end;

CREATE OR ALTER trigger ufiyguntar for urunler
active after update position 0
AS
begin
--  if (new.usonalfiyat<>old.usonalfiyat )  then
--  update urunler set USFIYDEGTAR=current_Date where uid=old.uid;

  if (new.usonsatfiy<>old.usonsatfiy) then
  Begin
  update urunler set USFIYDEGTAR=current_Date where uid=old.uid;
  execute procedure FIYAT_DUZELT(old.uid);
  End

end;

-- Raf Havuzu 2.fiyat değişim SQL leri
ALTER TABLE RAFBARKODU ADD RFSATFIYN FLOAT;

CREATE OR ALTER trigger raf_havuzu for rafbarkodu
active after insert position 0
AS
begin
  merge into rafbarkodu r
  using urunler u
  on r.rfurid=u.uid
  when matched then
  update set
  r.umensei=u.umensei,
  r.rfbrkdno=u.ubarkod,
  r.rfsatfiy=u.usonsatfiy,
  r.rfsatfiyn=u.usonsatfiyn,
  r.rfbrkdurad=u.uad;
end;

--Yazıcı Tablo ve Generator
CREATE SEQUENCE GEN_YAZICI_ID;

CREATE TABLE YAZICI (
    YID         BIGINT,
    YUID        BIGINT,
    YUBARKOD    VARCHAR(20),
    YUAD        VARCHAR(100),
    YTARIHI     DATE DEFAULT current_date,
    YSAATI      TIME DEFAULT current_time,
    YSATFIYN    FLOAT,
    YSATFIYK    FLOAT,
    YAZILDIMI   INTEGER DEFAULT 0,
    YKAGITTIPI  VARCHAR(10),
    YFDTAR      DATE,
    YADET       INTEGER DEFAULT 1
);

CREATE OR ALTER TRIGGER YAZICI_TR FOR YAZICI
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  IF(NEW.yid IS NULL) THEN
 NEW.yid = GEN_ID(gen_yazici_id , 1);
end;

CREATE OR ALTER trigger yazici_duzelt for yazici
active after insert position 0
AS
begin
  merge into yazici y
  using urunler u
  on y.yuid=u.uid
  when matched then
  update set
  y.yubarkod=u.ubarkod,
  y.ysatfiyk =u.usonsatfiy,
  y.ysatfiyN =u.usonsatfiyN,
  y.yuad =u.uad,
  Y.yfdtar=(Select first 1 utarih from ufiydeg as d where d.uid=y.yuid order by 1 desc);
end;