-- DB önemli Sweet intervali CMD
gfix -h 1000 db\2013.snc -user sysdba -password masterkey
-- --------------------------------------------------
-- Stok Tarih alanları
ALTER TABLE STOKDRM ADD STDRSTGTAR DATE, ADD STDRSONSTCIKTAR DATE
-- --------------------------------------------------
-- Silinmiş ürün stok silme..
delete from stok where stuid in ( Select stdruid from stokdrm where stdrubarkod is null);
delete from stok where stuid in ( Select stdruid from stokdrm where stdrubarkod='');
-- Stok alan düzeltmesi çok önemli 

-- --------------------------------------------------
-- **** stok daki stasid alanını bigint yap

DROP INDEX STOK_IDX5

CREATE INDEX STOK_IDX5
ON STOK (STASID)

-- --------------------------------------------------
-- Mobil Kullanıcı Ekle
insert into KUL (kulid, KULAD, KULSIFRE, KULADI, KULSOYADI) values (GEN_ID(gen_alfatdetay_id,1),'MOBIL', '1234', 'MOBIL', 'MOBIL');

-- --------------------------------------------------
-- Değiştirilemez Satış Fiyatı Alanı ekle.. 
ALTER TABLE SFATDETAY ADD SFATANATUTAR FLOAT;
	
-- --------------------------------------------------
-- Ürünlere Menşei Ekle 	
ALTER TABLE URUNLER ADD UMENSEI VARCHAR(30) DEFAULT 'İTHAL';
ALTER TABLE RAFBARKODU ADD UMENSEI VARCHAR(10) DEFAULT 'İTHAL';

-- --------------------------------------------------
-- Satış Tablo alanlar düzeltmeleri

ALTER TABLE SBASLIK ALTER COLUMN SATTOPLAM SET DEFAULT 0;
ALTER TABLE SBASLIK ALTER COLUMN SATISKTOPLAM SET DEFAULT 0;
ALTER TABLE SBASLIK ALTER COLUMN SATARATOPLAM SET DEFAULT 0;
ALTER TABLE SBASLIK ALTER COLUMN SATEKISKYUZDE SET DEFAULT 0;
ALTER TABLE SBASLIK ALTER COLUMN SATEKISKONTO SET DEFAULT 0;
ALTER TABLE SBASLIK ALTER COLUMN SATKDV1 SET DEFAULT 0;
ALTER TABLE SBASLIK ALTER COLUMN SATOTVTOP SET DEFAULT 0;
ALTER TABLE SBASLIK ALTER COLUMN SATGENELTOP SET DEFAULT 0;
ALTER TABLE SBASLIK ALTER COLUMN SATKDV2 SET DEFAULT 0;
ALTER TABLE SBASLIK ALTER COLUMN SATKDV3 SET DEFAULT 0;
ALTER TABLE SBASLIK ALTER COLUMN SATKDVTOP SET DEFAULT 0;
ALTER TABLE SBASLIK ALTER COLUMN SATKDVT1 SET DEFAULT 0;
ALTER TABLE SBASLIK ALTER COLUMN SATKDVT2 SET DEFAULT 0;
ALTER TABLE SBASLIK ALTER COLUMN SATKDVT3 SET DEFAULT 0;
ALTER TABLE SBASLIK ADD COLUMN SATFATNOGUN SET DEFAULT 0;
ALTER TABLE SBASLIK ALTER COLUMN SATAK SET DEFAULT 'A';
ALTER TABLE SBASLIK ALTER COLUMN SATTARIHI SET DEFAULT current_date;
ALTER TABLE SBASLIK ALTER COLUMN SATVADETARIHI SET DEFAULT current_date;
ALTER TABLE SFATDETAY ALTER COLUMN SFATKDV SET DEFAULT 0;
ALTER TABLE SFATDETAY ALTER COLUMN SFATOTV SET DEFAULT 0;
ALTER TABLE SFATDETAY ALTER COLUMN SFATTUTAR SET DEFAULT 0;
ALTER TABLE SFATDETAY ALTER COLUMN SFATARATOPLAM SET DEFAULT 0;
ALTER TABLE SFATDETAY ALTER COLUMN SFATISK1 SET DEFAULT 0;
ALTER TABLE SFATDETAY ALTER COLUMN SFATISK2 SET DEFAULT 0;
ALTER TABLE SFATDETAY ALTER COLUMN SFATISK3 SET DEFAULT 0;
ALTER TABLE SFATDETAY ALTER COLUMN SFATISKTOPLAM SET DEFAULT 0;
ALTER TABLE SFATDETAY ALTER COLUMN SFATKDVTOPLAM SET DEFAULT 0;
ALTER TABLE SFATDETAY ALTER COLUMN SFATOTVTOPLAM SET DEFAULT 0;
ALTER TABLE SFATDETAY ALTER COLUMN SFATTOPLAM SET DEFAULT 0;
ALTER TABLE SFATDETAY ALTER COLUMN SFATALMALIYET SET DEFAULT 0;
ALTER TABLE SFATDETAY ALTER COLUMN SFATANATUTAR SET DEFAULT 0;
ALTER TABLE SFATDETAY ALTER COLUMN SFATMIKTAR SET DEFAULT 1;
ALTER TABLE SFATDETAY ALTER COLUMN SFATTAR SET DEFAULT current_date;
ALTER TABLE SFATDETAY ALTER COLUMN SFATAK SET DEFAULT 'A';

-- --------------------------------------------------
-- Alış Tablo alanlar düzeltmeleri
ALTER TABLE ALFATDETAY ADD ALFMALIYET FLOAT DEFAULT 0;
ALTER TABLE ALFATDETAY ALTER COLUMN ALFMIKTAR SET DEFAULT 0;
ALTER TABLE ALFATDETAY ALTER COLUMN ALFBIRIM SET DEFAULT 'ADET';
ALTER TABLE ALFATDETAY ALTER COLUMN ALFKDV SET DEFAULT 0;
ALTER TABLE ALFATDETAY ALTER COLUMN ALFOTV SET DEFAULT 0;
ALTER TABLE ALFATDETAY ALTER COLUMN ALFTUTAR SET DEFAULT 0;
ALTER TABLE ALFATDETAY ALTER COLUMN ALFATARATOPLAM SET DEFAULT 0;
ALTER TABLE ALFATDETAY ALTER COLUMN ALFIST1 SET DEFAULT 0;
ALTER TABLE ALFATDETAY ALTER COLUMN ALFISK2 SET DEFAULT 0;
ALTER TABLE ALFATDETAY ALTER COLUMN ALFISK3 SET DEFAULT 0;
ALTER TABLE ALFATDETAY ALTER COLUMN ALFISKTOPLAM SET DEFAULT 0;
ALTER TABLE ALFATDETAY ALTER COLUMN ALFKDVTOPLAM SET DEFAULT 0;
ALTER TABLE ALFATDETAY ALTER COLUMN ALFOTVTOPLAM SET DEFAULT 0;
ALTER TABLE ALFATDETAY ALTER COLUMN ALFTOPLAM SET DEFAULT 0;
ALTER TABLE ALFATDETAY ALTER COLUMN ALFATAK SET DEFAULT 'A';

ALTER TABLE ALFATBASLIK ALTER COLUMN ALFATTOPLAM SET DEFAULT 0;
ALTER TABLE ALFATBASLIK ALTER COLUMN ALFATISKTOPLAM SET DEFAULT 0;
ALTER TABLE ALFATBASLIK ALTER COLUMN ALFATARATOPLAM SET DEFAULT 0;
ALTER TABLE ALFATBASLIK ALTER COLUMN ALFATEKISKYUZDE SET DEFAULT 0;
ALTER TABLE ALFATBASLIK ALTER COLUMN ALFATEKISKONTO SET DEFAULT 0;
ALTER TABLE ALFATBASLIK ALTER COLUMN ALFATKDV1 SET DEFAULT 0;
ALTER TABLE ALFATBASLIK ALTER COLUMN ALFATOTVTOP SET DEFAULT 0;
ALTER TABLE ALFATBASLIK ALTER COLUMN ALFATGENELTOP SET DEFAULT 0;
ALTER TABLE ALFATBASLIK ALTER COLUMN ALFATAK SET DEFAULT 'A';
ALTER TABLE ALFATBASLIK ALTER COLUMN ALFATKDV2 SET DEFAULT 0;
ALTER TABLE ALFATBASLIK ALTER COLUMN ALFATKDV3 SET DEFAULT 0;
ALTER TABLE ALFATBASLIK ALTER COLUMN ALFATKDVTOP SET DEFAULT 0;
ALTER TABLE ALFATBASLIK ALTER COLUMN ALFATKDV1MTRH SET DEFAULT 0;
ALTER TABLE ALFATBASLIK ALTER COLUMN ALFATKDV2MTRH SET DEFAULT 0;
ALTER TABLE ALFATBASLIK ALTER COLUMN ALFATKDV3MTRH SET DEFAULT 0;




-- Ara Toplam Hesapla
create or alter procedure ARA_TOPLAM_HESAPLA (
    STID integer)
returns (
    URUNTOP float,
    KDV float,
    OTV float,
    ISK float)
as
begin
  select coalesce(sum(SFATARATOPLAM), 0), coalesce(sum(SFATKDV), 0), coalesce(sum(SFATOTV), 0),coalesce(sum(SFATISKTOPLAM), 0)
  from SFATDETAY
  where SFATBASID = :STID
  into URUNTOP, KDV, OTV, ISK;

  suspend;
end

GRANT SELECT ON SFATDETAY TO PROCEDURE ARA_TOPLAM_HESAPLA;
GRANT EXECUTE ON PROCEDURE ARA_TOPLAM_HESAPLA TO SYSDBA;
-- --------------------------------------------------
-- Cari Kart Sil
create or alter procedure CK_SIL (STID integer)
as
begin
delete from carikart where ckasfid=:stid;
suspend;
end;

GRANT SELECT,DELETE ON CARIKART TO PROCEDURE CK_SIL;
GRANT EXECUTE ON PROCEDURE CK_SIL TO SYSDBA
-- --------------------------------------------------
-- Grup Düzelt
create or alter procedure GRUP_DUZELT
as
begin
  merge into SFATDETAY as S
  using URUNLER as U
  on S.SFATUID = U.UID
  when matched then
      update set S.SFATUGR1 = U.UGRUP1,
                 S.SFATUGR2 = U.UGRUP2,
                 S.SFATURUNAD = U.UAD,
                 S.SFATURUNBARKOD = U.UBARKOD;

end;

GRANT EXECUTE ON PROCEDURE GRUP_DUZELT TO SYSDBA; 
GRANT SELECT,DELETE,UPDATE ON SFATDETAY TO PROCEDURE GRUP_DUZELT;

-- --------------------------------------------------
-- Maliyet Hesapla
create or alter procedure MALIYET_HESAPLA (STID integer)
as
begin
  update SFATDETAY
  set SFATALMALIYET = (select round(USONALFIYAT, 2) from URUNLER where UID = SFATUID) * SFATMIKTAR,
      sfatanatutar=(select round(USONsatfiy, 2)     from URUNLER where UID = SFATUID) * SFATMIKTAR
  where sfatbasid=:stid;
  suspend;
end;

GRANT SELECT,UPDATE ON SFATDETAY TO PROCEDURE MALIYET_HESAPLA;
GRANT SELECT ON URUNLER TO PROCEDURE MALIYET_HESAPLA;
GRANT EXECUTE ON PROCEDURE MALIYET_HESAPLA TO SYSDBA;

-- --------------------------------------------------
-- SAtış Satır Hesapla
create or alter procedure SATIS_SATIR_HESAPLA (
    STID integer)
as
begin
  update SFATDETAY
  set SFATARATOPLAM = SFATMIKTAR * SFATTUTAR
  where SFATBASID = :STID;
  --  update sfatdetay set sfatisktoplam=0 where sfatbasid=:stid;
  --  update sfatdetay set sfatisktoplam=sfatisktoplam+SFATARATOPLAM*sfatist1/100 where sfatbasid=:stid;
  --  update sfatdetay set sfatisktoplam=sfatisktoplam+(SFATARATOPLAM-sfatisktoplam)*sfatisk2/100 where sfatbasid=:stid;
  --  update sfatdetay set sfatisktoplam=sfatisktoplam+(SFATARATOPLAM-sfatisktoplam)*sfatisk3/100 where sfatbasid=:stid;
  --  update sfatdetay set SFATKDVTOPLAM=((sfataratoplam-sfatisktoplam)*SFATKDV)/100 where sfatbasid=:stid;
  --  update sfatdetay set SFATOTVTOPLAM=((sfataratoplam-sfatisktoplam)*SFATOTV)/100 where sfatbasid=:stid;
  update SFATDETAY
  set SFATTOPLAM = SFATARATOPLAM -- +SFATKDVTOPLAM+SFATOTVTOPLAM-sfatisktoplam
  where SFATBASID = :STID;
  update SBASLIK
  set SATTOPLAM = round((select sum(SFATTOPLAM)
                         from SFATDETAY
                         where SFATBASID = :STID), 2)
  where SATID = :STID;
  suspend;
end;

GRANT SELECT,UPDATE ON SFATDETAY TO PROCEDURE SATIS_SATIR_HESAPLA;
GRANT SELECT,UPDATE ON SBASLIK TO PROCEDURE SATIS_SATIR_HESAPLA;
GRANT EXECUTE ON PROCEDURE SATIS_SATIR_HESAPLA TO SYSDBA;

-- --------------------------------------------------
-- Stok Durum Düzelt
create or alter procedure STOK_DRM_DUZELT
as
begin
  merge into stokdrm s
  using urunler u
  on s.stdruid=u.uid
  when matched then
  update set
  s.stdruad=u.uad,
  s.stdrubarkod=u.ubarkod,
  s.stdrgrup1=u.ugrup1,
  s.stdrgrup2=u.ugrup2,
  s.stdrgrup3=u.ugrup3,
  s.stdraltsnr=u.udepoaltsinir,
  s.stdrustsnr=u.udepoustsinir,
  s.stdralfiy=round(u.usonalfiyat,2),
  s.stdrsatfiy=round(u.usonsatfiy,2),
  s.stdrfirma=u.ufirma;
end;

GRANT SELECT,UPDATE ON STOKDRM TO PROCEDURE STOK_DRM_DUZELT;
GRANT SELECT ON URUNLER TO PROCEDURE STOK_DRM_DUZELT;
GRANT EXECUTE ON PROCEDURE STOK_DRM_DUZELT TO "PUBLIC";
GRANT EXECUTE ON PROCEDURE STOK_DRM_DUZELT TO SYSDBA;

-- --------------------------------------------------
-- Stok Düzelt
create or alter procedure STOK_DUZELT
as
begin
  Delete from stok where stuid is null;
  Delete from stok where stuad is null;
  Delete from stok where stuid='';
  Delete from stok where stbarkod is null;
  Delete from stok where stbarkod='';

  -- merge into stok s
  -- using urunler u
  -- on s.stuid=u.uid
  -- when matched then
  -- update set
  -- s.stuad=u.uad,
  -- s.stbarkod=u.ubarkod;
end;

GRANT SELECT,DELETE,UPDATE ON STOK TO PROCEDURE STOK_DUZELT;
GRANT SELECT ON URUNLER TO PROCEDURE STOK_DUZELT;
GRANT EXECUTE ON PROCEDURE STOK_DUZELT TO "PUBLIC";
GRANT EXECUTE ON PROCEDURE STOK_DUZELT TO SYSDBA;

-- --------------------------------------------------
-- Stok Ekle

create or alter procedure ST_EKLE (
    STID integer,
    ISLEM varchar(10))
as
begin
  delete from STOK
  where STASID = :STID;
  if (ISLEM = 's') then
    insert into STOK (STASFNO, STASID, STUID, STUAD, STBARKOD, STISTARIH, STTIP, STADET)
    select :STID, :STID, SFATUID, SFATURUNAD, SFATURUNBARKOD, SFATTAR, 'satış' as DURUM, sum(SFATMIKTAR * -1)
    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)
    select :STID, :STID, SFATUID, SFATURUNAD, SFATURUNBARKOD, SFATTAR, 'alış' as DURUM, sum(SFATMIKTAR)
    from SFATDETAY
    where SFATBASID = :STID and
          SFATAK = 'A'
    group by :STID, :STID, SFATUID, SFATURUNAD, SFATURUNBARKOD, SFATTAR, DURUM;
  suspend;
end;
GRANT SELECT,INSERT,DELETE ON STOK TO PROCEDURE ST_EKLE;
GRANT SELECT ON SFATDETAY TO PROCEDURE ST_EKLE;
GRANT EXECUTE ON PROCEDURE ST_EKLE TO SYSDBA;

	
	
-- --------------------------------------------------
-- Ürün Foto tablosu	

CREATE TABLE URUNFOTO (
    UFID      BIGINT,
    UFUID     BIGINT,
    UFBARKOD  VARCHAR(20),
    UFAD      VARCHAR(100),
    UFBFOTO   BLOB SUB_TYPE 0 SEGMENT SIZE 16384,
    UFKFOTO   BLOB SUB_TYPE 0 SEGMENT SIZE 4096,
    UFTARIH   TIMESTAMP,
    UFKUL     VARCHAR(5)
);
	
CREATE SEQUENCE GEN_URUNFOTO_ID;
ALTER SEQUENCE GEN_URUNFOTO_ID RESTART WITH 0;	

-- --------------------------------------------------
-- Yeni KASA Ekran View'ı
CREATE OR ALTER VIEW KASA_GOSTER(
    SATTARIHI,
    SATTIPI,
    SATKUL,
    TOP)
AS
select SATTARIHI, SATTIPI, SATKUL, round(sum(SATGENELTOP), 2) as TOP
from  SBASLIK
where SATFIRMAID <> 2
group by SATTARIHI, SATTIPI, SATKUL
union all
select SATTARIHI, SATTIPI, SATKUL, round(sum(SATGENELTOP), 2) as TOP
from  SBASLIK
where SATFIRMAID = 2
group by SATTARIHI, SATTIPI, SATKUL
union all
select GTAR as SATTARIHI, 'GİDER' as SATTIPI, GCIKKASA as SATKUL, round(sum(GTUTAR), 2) as TOP
from  GIDER
group by SATTARIHI, SATTIPI, SATKUL
union all
select SFATTAR as SATTARIHI, 'İSKONTO' as SATTIPI, SATKUL as SATKUL, round(sum(SFATTOPLAM - SFATANATUTAR), 2) as TOP
from  SFATDETAY
left  join SBASLIK on SFATDETAY.SFATBASID = SBASLIK.SATFATNO
where SFATTOPLAM <> SFATANATUTAR  and SFATTOPLAM>0
group by SFATTAR, SATTIPI, SATKUL
order by 1, 3
;

-- --------------------------------------------------
-- Kar raporu Alım maliyeti düzeltme update i..
merge into sfatdetay a using urunler b on a.sfatuid=b.uid  
and when matched then  update set a.sfatalmaliyet =b.usonalfiyat*a.sfatmiktar;

-- --------------------------------------------------
-- Excel Ürün Alımı trigger ve SP si..
CREATE or alter trigger alfexcel for alfexcel
active before insert position 0
AS
begin
IF(NEW.aeid IS NULL) THEN
begin
NEW.aeid = GEN_ID(gen_alfexcel_id, 1);
end
end;


create or alter procedure ALF_EXCEL
as
begin
  update alfexcel
  set AEURUNMALIYET=AEALFIYAT-AEALFIYAT*aeisk1/100;
  update alfexcel
  set AEURUNMALIYET=AEURUNMALIYET-AEURUNMALIYET*aeisk2/100;
  update alfexcel
  set AEURUNMALIYET=AEURUNMALIYET-AEURUNMALIYET*aeisk3/100;
  update alfexcel
  set AEURUNMALIYET=AEURUNMALIYET+AEURUNMALIYET*aekdv/100;
  update alfexcel 
  set AEURUNMALIYET=round(AEURUNMALIYET,2);
  suspend;
end;

GRANT SELECT,UPDATE ON ALFEXCEL TO PROCEDURE ALF_EXCEL;
GRANT EXECUTE ON PROCEDURE ALF_EXCEL TO SYSDBA;

-- --------------------------------------------------
-- Raf Havuzu Düzenle..

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.rfbrkdurad=u.uad,
  r.rfsatfiy=replace(cast(round(u.usonsatfiy,2) as Varchar(10)),'.',',');
end;


-- --------------------------------------------------
-- Alış Satır Hesapla..
create or alter procedure ALF_HESAPLA (
    ALID integer)
as
begin
  update ALFATDETAY
  set ALFATARATOPLAM = ALFTUTAR
  - (ALFTUTAR * ALFIST1 / 100)
  - (ALFTUTAR - ALFTUTAR * ALFIST1 / 100) * ALFISK2 / 100
  - (alftutar-(ALFTUTAR * ALFIST1 / 100)-(ALFTUTAR - ALFTUTAR * ALFIST1 / 100) * ALFISK2 / 100)*alfisk3/100
  +
  (
  ALFTUTAR
  - (ALFTUTAR * ALFIST1 / 100)
  - (ALFTUTAR - ALFTUTAR * ALFIST1 / 100) * ALFISK2 / 100
  - (alftutar-(ALFTUTAR * ALFIST1 / 100)-(ALFTUTAR - ALFTUTAR * ALFIST1 / 100) * ALFISK2 / 100)*alfisk3/100
  )*alfkdv/100,
  alfisktoplam=
    ((ALFTUTAR * ALFIST1 / 100)
  + (ALFTUTAR - ALFTUTAR * ALFIST1 / 100) * ALFISK2 / 100
  + (alftutar-(ALFTUTAR * ALFIST1 / 100)-(ALFTUTAR - ALFTUTAR * ALFIST1 / 100) * ALFISK2 / 100)*alfisk3/100)*alfmiktar,
  alfkdvtoplam=((
  ALFTUTAR
  - (ALFTUTAR * ALFIST1 / 100)
  - (ALFTUTAR - ALFTUTAR * ALFIST1 / 100) * ALFISK2 / 100
  - (alftutar-(ALFTUTAR * ALFIST1 / 100)-(ALFTUTAR - ALFTUTAR * ALFIST1 / 100) * ALFISK2 / 100)*alfisk3/100
  )*alfkdv/100)*alfmiktar
  where ALFBASID = :ALID;
  update alfatdetay
  set alftoplam= ALFATARATOPLAM*alfmiktar
  where ALFBASID = :ALID;
  suspend;
end

create or alter procedure URUN_SIL (
    S_ID integer)
as
begin
  Delete from stok where stuid=:s_id;
  Delete from urunler where uid= :s_id;
  suspend;
end

create or alter procedure ST_TARIH
as
begin
  update STOKDRM
  set STDRSONSTCIKTAR = (select first 1 STISTARIH from STOK
                         where STOK.STUID = STOKDRM.STDRUID and
                               STOK.STADET < 0
                         order by 1 desc);

      update STOKDRM
  set STDRSTGTAR = (select first 1 STISTARIH from STOK
                    where STOK.STUID = STOKDRM.STDRUID and
                          STOK.STADET > 0);

      suspend;
end


  update SFATDETAY
  set SFATALMALIYET =  sfatalmaliyet*-1
  where
  SFATBASID in (Select satid from sbaslik where satfirmaid=2)
  and   SFATALMALIYET>=0


  Select * from sfatdetay
  where
  SFATBASID in (Select satid from sbaslik where satfirmaid=2)
  and   SFATALMALIYET>=0