Project

General

Profile

Download (2.31 KB)

Požadavek #93 » fce.sql

Vítězslav Dvořák, 03/19/2025 09:09 PM

 
create
or replace function dgetstavnaskladeaktualni(
i_pocatecni boolean,
i_idstredisko integer,
i_idcenik integer,
i_idsklad integer,
i_datum date,
_idceniks integer []
) RETURNS numeric(19, 6) AS $ $ declare _iducetniobdobi integer;

_stavMj numeric(19, 6);

_idSkladKarty integer;

_sadaItemCount integer;

_polozkaSady record;

_stavSkladuSady numeric(19, 6);

_pocetPozadavku numeric(19, 6);

_skladove boolean;

_idskladFinal integer;

begin
select
count(*) into _sadaItemCount
from
csady
where
idceniksada = i_idCenik
and idcenik <> csady.idceniksada;

if(_sadaItemCount = 0) then
select
skladove into _skladove
from
ccenik
where
idcenik = i_idcenik;

if(_skladove) then
select
idUcetObdobi into _iducetniobdobi
from
nUcetObdobi
where
platiOdData <= i_datum
and platiDoData >= i_datum;

select
idKarty into _idSkladKarty
from
sKarty
where
idCenik = i_idCenik
and idSkladu = i_idsklad
and idUcetObdobi = _iducetniobdobi;

if i_pocatecni then
select
pocatMj into _stavMj
from
skarty
where
idkarty = _idSkladKarty;

else
select
stavMj into _stavMj
from
skarty
where
idkarty = _idSkladKarty;

select
sum(mnozMjPlan) into _pocetPozadavku
from
dpolsklad
where
mnozMjPlan <> 0.0
and mnozMj = 0.0
and idcenik = i_idCenik
and idbspskl = i_idsklad
and storno = false;

if(_pocetPozadavku is not null) then _stavMj = _stavMj - _pocetPozadavku;

end if;

end if;

else _stavMj = 9999999999.0;

end if;

else for _polozkaSady in
select
*
from
cSady
where
idCenikSada = i_idCenik
and cSady.idcenik <> cSady.idceniksada loop perform checkrekurzecenik(i_idcenik, _idceniks);

select
getPrefIdSkladu(
i_idstredisko,
(
select
idSkupZboz
from
ccenik
where
idcenik = _polozkaSady.idCenik
),
_polozkaSady.idCenik
) into _idskladFinal;

if (_idskladFinal is null) then _idskladFinal := i_idsklad;

end if;

select
dGetStavNaSkladeAktualni(
i_pocatecni,
i_idstredisko,
_polozkaSady.idCenik,
_idskladFinal,
i_datum,
_idceniks || i_idcenik
) into _stavSkladuSady;

if(_polozkaSady.mnozMj <> 0) then _stavSkladuSady = _stavSkladuSady / _polozkaSady.mnozMj;

if(_stavMj is null) then _stavMj := 9999999999.0;

end if;

select
minValue(_stavMj, _stavSkladuSady) into _stavMj;

end if;

end loop;

end if;

if(_stavMj is null) then _stavMj := 0;

end if;

RETURN _stavMj;

end $ $ LANGUAGE plpgsql;
    (1-1/1)