-- Yeni Yıl Güncellemesi
EXECUTE block as
BEGIN
if (not exists(
select 1 from RDB$RELATION_FIELDS rf
where rf.RDB$RELATION_NAME = 'FIRMABILGI' and rf.RDB$FIELD_NAME = 'YIL'))
then
execute statement 'ALTER TABLE FIRMABILGI ADD YIL INTEGER';
END

update firmabilgi set yil=2021;

-- Ürünler OBRK2 Düzletme
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)||ASCII_CHAR(13)||ASCII_CHAR(10)||'%';


-- Ürün Sil SP deüzeltmesi

CREATE OR ALTER procedure URUN_SIL (
    S_ID integer)
as
begin
  Delete from stok where stuid= :s_id;
  Delete from ufiydeg where uid= :s_id;
  Delete from urunler where uid= :s_id;
  suspend;
end;


-- Ürünler Düzeltme

ALTER TABLE URUNLER ALTER COLUMN USTKGTAR
SET DEFAULT current_date;

CREATE OR ALTER TRIGGER UKOD_DUZELT FOR URUNLER
ACTIVE AFTER INSERT POSITION 0
AS
begin
  if (new.uid is not null) then
  update urunler set ukod=uid where uid=new.uid;
end

ALTER TABLE URUNLER
    ADD USFIYDEGTAR DATE DEFAULT current_date;
	
update urunler set USFIYDEGTAR = (Select first 1 utarih from ufiydeg where ufiydeg.uid=urunler.uid order by 1 desc);

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
  update urunler set USFIYDEGTAR=current_Date where uid=old.uid;
end;

update urunler set USFIYDEGTAR = (Select first 1 utarih from ufiydeg where utarih is not null order by utarih) where usfiydegtar is null;
	

-- Ürün Fiy Değ. TRIGGER

CREATE OR ALTER TRIGGER UFIYDEG_TR FOR UFIYDEG
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.UFID is null) then
    new.UFID = gen_id(GEN_UFIYDEG_ID, 1);
end


-- Kasa Sağlama Tablosu

CREATE TABLE KASA (
    K_ID         BIGINT NOT NULL,
    KASA_ADI     VARCHAR(10),
    NAKIT        FLOAT,
    BANKA_1      FLOAT,
    BANKA_2      FLOAT,
    BANKA_3      FLOAT,
    BANKA_4      FLOAT,
    BANKA_5      FLOAT,
    BOZUK        FLOAT,
    CIKAN        FLOAT,
    ANA_PARA     FLOAT,
    KASA_TOPLAM  FLOAT,
    PC_TOPLAM    FLOAT,
    SONUC        FLOAT,
    EKSIK        FLOAT,
    FAZLA        FLOAT
);


-- K_ID Generatoru oluştur

CREATE GENERATOR GEN_KASA_ID;

CREATE OR ALTER TRIGGER KASA FOR KASA
ACTIVE BEFORE INSERT POSITION 0
AS
begin
IF(NEW.K_ID IS NULL) THEN
begin
NEW.K_ID = GEN_ID(gen_kasa_id , 1);
end
end


-- alış faturası genel toplam prosedürü

create or alter procedure ALF_TOPLAM_HESAPLA (
    ALID integer)
as
begin
  update ALFATBASLIK
  set ALFATGENELTOP = (select sum(ALFTOPLAM)
                       from ALFATDETAY
                       where ALFBASID = :ALID),
      ALFATARATOPLAM = (select sum(ALFATARATOPLAM)
                        from ALFATDETAY
                        where ALFBASID = :ALID),
      ALFATKDVTOP = (select sum(ALFKDVTOPLAM)
                     from ALFATDETAY
                     where ALFBASID = :ALID),
      ALFATOTVTOP = (select sum(ALFATOTVTOP)
                     from ALFATDETAY
                     where ALFBASID = :ALID)

  where ALFATNO = :ALID;

  update ALFATBASLIK
  set ALFATEKISKONTO = ALFATARATOPLAM - (ALFATARATOPLAM * ALFATEKISKYUZDE / 100)
  where ALFATNO = :ALID;

  suspend;
end


-- stok alan eklemesi

ALTER TABLE STOK ADD STGTUR VARCHAR(1) DEFAULT 'i';

update stok set stgtur='i' where stasfno='ILK STOK';
update stok set stgtur='a' where stasfno<>'ILK STOK' and sttip='alış';
update stok set stgtur='s' where stasfno<>'ILK STOK' and sttip='satış';


-- st_ekle prosedür düzeltmesi

CREATE OR ALTER procedure ST_EKLE (
    STID integer,
    ISLEM varchar(10))
as
begin
  delete from STOK
  where STASID = :STID and stgtur='s';
  if (ISLEM = 's') then
    insert into STOK (STASFNO, STASID, STUID, STUAD, STBARKOD, STISTARIH, STTIP, STADET,stgtur)
    select :STID, :STID, SFATUID, SFATURUNAD, SFATURUNBARKOD, SFATTAR, 'satış' as DURUM, sum(SFATMIKTAR * -1),'s'
    from SFATDETAY
    where SFATBASID = :STID and
          SFATAK = 'A'
    group by :STID, :STID, SFATUID, SFATURUNAD, SFATURUNBARKOD, SFATTAR, DURUM;
  else
    insert into STOK (STASFNO, STASID, STUID, STUAD, STBARKOD, STISTARIH, STTIP, STADET,stgtur)
    select :STID, :STID, SFATUID, SFATURUNAD, SFATURUNBARKOD, SFATTAR, 'alış' as DURUM, sum(SFATMIKTAR),'s'
    from SFATDETAY
    where SFATBASID = :STID and
          SFATAK = 'A'
    group by :STID, :STID, SFATUID, SFATURUNAD, SFATURUNBARKOD, SFATTAR, DURUM;
  suspend;
end;


-- Kasiyer Sağlama Ekranı

CREATE TABLE KASIYER (
    KID         INTEGER NOT NULL,
    TARIH       DATE DEFAULT current_date,
    KASA_ADI    VARCHAR(5),
    NAKIT       FLOAT DEFAULT 0,
    BANKA1      FLOAT DEFAULT 0,
    BANKA2      FLOAT DEFAULT 0,
    BANKA3      FLOAT DEFAULT 0,
    BANKA4      FLOAT DEFAULT 0,
    BANKA5      FLOAT DEFAULT 0,
    POSTOPLAM   FLOAT DEFAULT 0,
    BOZUK       FLOAT DEFAULT 0,
    CIKAN       FLOAT DEFAULT 0,
    ANAPARA     FLOAT DEFAULT 0,
    KASATOPLAM  FLOAT DEFAULT 0,
    PCTOPLAM    FLOAT DEFAULT 0,
    SONUC       FLOAT DEFAULT 0,
    EKSIK       FLOAT DEFAULT 0,
    FAZLA       FLOAT DEFAULT 0
);

CREATE SEQUENCE GEN_KASIYER_ID; 
	
	
create or alter trigger KASIYER_TRG for KASIYER
active before insert position 0
as
begin
  if (new.KID is null) then
  begin
    new.KID = gen_id(GEN_KASIYER_ID, 1);
    new.tarih = current_date;
  end
end;


-- SBaşlık Düzelmtesi
ALTER TABLE SBASLIK ALTER COLUMN SATBASSAAT
SET DEFAULT current_time;

CREATE OR ALTER procedure SATIS_HESAPLA (
    STID integer)
as
begin
  update SBASLIK
  set SATTOPLAM = (select sum(SFATTOPLAM)
                   from SFATDETAY
                   where SFATBASID = :STID),
      SATARATOPLAM = (select sum(SFATTOPLAM) - sum(SFATISKTOPLAM)
                      from SFATDETAY
                      where SFATBASID = :STID),
      SATKDVTOP = (select sum(SFATKDVTOPLAM)
                   from SFATDETAY
                   where SFATBASID = :STID),
      SATOTVTOP = (select sum(SFATOTVTOPLAM)
                   from SFATDETAY
                   where SFATBASID = :STID),
      SATBITSAAT = current_time
  where SATFATNO = :STID;

  update SBASLIK
  set SATEKISKONTO = SATARATOPLAM - (SATARATOPLAM * SATEKISKYUZDE / 100)
  where SATFATNO = :STID;

  update SBASLIK
  set SATGENELTOP = SATARATOPLAM + SATKDVTOP + SATOTVTOP - SATISKTOPLAM - SATEKISKONTO
  where SATFATNO = :STID;
  suspend;
end;


-- Silinen Satış

CREATE SEQUENCE GEN_SATIS_SILINEN_ID;
ALTER SEQUENCE GEN_SATIS_SILINEN_ID RESTART WITH 0;

CREATE TABLE SATIS_SILINEN (
    SSID       BIGINT NOT NULL,
    SSTARIH    DATE DEFAULT current_date,
    SSSAAT     TIME DEFAULT current_time,
	SSSATNO    VARCHAR(10),
    SSKASIYER  VARCHAR(5),
    SSBARKOD   VARCHAR(20),
    SSUAD      VARCHAR(100),
    SSFIYAT    FLOAT,
    SSADET     INTEGER
);

ALTER TABLE SATIS_SILINEN ADD CONSTRAINT PK_SATIS_SILINEN PRIMARY KEY (SSID);


CREATE OR ALTER TRIGGER Sat_sil FOR SATIS_SILINEN
ACTIVE BEFORE INSERT POSITION 0
AS
begin
IF(NEW.SSID IS NULL) THEN
begin
NEW.SSID = GEN_ID(GEN_SATIS_SILINEN_ID , 1);
end
end;


CREATE OR ALTER trigger silinen_ekle for sfatdetay
active before insert or 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