-- Ürün barkod Birleştirme Hata kontrolüü

Select * from stok where not exists (select uid from urunler where urunler.uid=stok.stuid);

--Raf Rabkodu Alan düzeltmesi
ALTER DOMAIN RDB$95 SET DEFAULT 0;

update RDB$RELATION_FIELDS set
RDB$COLLATION_ID = -1
where (RDB$FIELD_NAME = 'RFSATFIY') and
(RDB$RELATION_NAME = 'RAFBARKODU');


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.rfbrkdurad=u.uad;
end



-- 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=2022;

-- Ürünler Alan düzeltmesi

ALTER TABLE URUNLER ALTER COLUMN UDEPOALTSINIR
SET DEFAULT 2;

ALTER TABLE URUNLER ALTER COLUMN UDEPOUSTSINIR
SET DEFAULT 50;

ALTER TABLE URUNLER ALTER COLUMN UID
DROP DEFAULT;


-- Satış silinen düzeltmesi

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


-- ürün ad grup düzeltmesi
Update urunler
set
uad=upper(uad),
ugrup1=upper(ugrup1),
ugrup2=upper(ugrup2),
ugrup3=upper(ugrup3),
ufirma=upper(ufirma);


-- Ü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ünler UID Trigger düzeltmesi
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;
  update urunler set uid=GEN_ID(GEN_URUNLER_ID, 1) where uid is null;
end

-- Sbaşlık Satış başlama / bitiş  saati düzeltmesi
CREATE OR ALTER trigger sbastrg for sbaslik
active before insert position 0
AS
begin
IF(NEW.satid IS NULL) THEN
begin
NEW.satid = GEN_ID(gen_sbaslik_id, 1);
new.sattarihi=current_date;
new.satvadetarihi=current_date;
new.satak='A';
new.satfatno=new.satid;
new.satbassaat=current_time;
end
end;

CREATE OR ALTER procedure SATIS_SATIR_HESAPLA (
    STID integer)
as
begin
  update SFATDETAY
  set SFATARATOPLAM = SFATMIKTAR * SFATTUTAR
  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), satbitsaat=current_time
  where SATID = :STID;
  suspend;
end;

-- Stok Say Yeni SP

create or alter procedure STOK_SAY
as
begin
  delete from STOK
  where STUID is null or STUAD is null or STUID = '' or STBARKOD is null or STBARKOD = '';

  delete from STOKDRM;


 insert into STOKDRM (STDRUID,STDRUBARKOD,STDRUAD, STDRTOPLAM,STDRSTGTAR,STDRSONSTCIKTAR,STDRTOPSAT,STDRTOPAL )
  select STUID,stbarkod,stuad, sum(STADET) as TOPLAM , min(stistarih), max(stistarih), sum(case when stadet<0 then stadet else 0 end ),sum(case when stadet>0 then stadet else 0 end )
  from STOK
  group by STUID,stbarkod,stuad;

  merge into STOKDRM S
  using URUNLER U
  on S.STDRUID = U.UID
  when matched then
      update set 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;

  suspend;
end;

--Ürünler Barkod Birleştirme Stok-Sdetay-Aldetay düzeltme SP si

create or alter procedure URUN_BIRLESTIR (
    EUID bigint not null,
    UID bigint not null,
    UBARKOD varchar(20) not null,
    UAD varchar(100) not null)
as
begin
  update STOK
  set STUID = :UID,
      STUAD = :UAD,
      STBARKOD = :UBARKOD
  where STUID = :EUID;

  update SFATDETAY
  set SFATUID = :UID,
      SFATURUNAD = :UAD,
      SFATURUNBARKOD = :UBARKOD
  where SFATUID = :EUID;

  update ALFATDETAY
  set ALFATUID = :UID,
      ALFURUNAD = :UAD,
      ALFURUNBARKOD = :UBARKOD
  where ALFATUID = :EUID;

  suspend;
end