"localhost:/firebird/data/ILICHEVSK2.FDB". Процедуры

GETWORKPARTIES

Входные параметры

Выходные параметры

Описание

Возвращает список робочих партий активніх на данній момент или на указанную дату

Определение

CREATE PROCEDURE GETWORKPARTIES(
    ISCURRENT INTEGER,
    PARTYDATE DATE,
    CROPID INTEGER,
    CLASSID INTEGER,
    STORAGEID INTEGER)
RETURNS (
    PARTYID INTEGER,
    BEGINDATE DATE)
AS
begin
  if (:ISCURRENT = 1) then
    for
      select PARTIES.PARTYID, PARTIES.BEGINDATE
        from PARTIES
        where (PARTIES.STATE = 1)
          and ((coalesce(:CROPID,-1) = -1)
            or ((PARTIES.CROPID = :CROPID)
              and ((PARTIES.CLASSID = :CLASSID) or (coalesce(:CLASSID,-1) = -1) or (PARTIES.CLASSID is null))))
          and ((PARTIES.STORAGEID = :STORAGEID) or (coalesce(:STORAGEID,-1) = -1))
      into :PARTYID, :BEGINDATE
    do suspend;
  else
    for
      select PARTIES.PARTYID, PARTIES.BEGINDATE
        from PARTIES
        where (:PARTYDATE between PARTIES.BEGINDATE
                            and coalesce(PARTIES.ENDDATE,
                                         case
                                           when (:PARTYDATE > current_date) then
                                             :PARTYDATE
                                           else current_date
                                         end))
           and ((coalesce(:CROPID,-1) = -1) or (PARTIES.CROPID = :CROPID))
           and ((PARTIES.CLASSID = :CLASSID) or (coalesce(:CLASSID,-1) = -1) or (PARTIES.CLASSID is null))
           and ((PARTIES.STORAGEID = :STORAGEID) or (coalesce(:STORAGEID,-1) = -1))
      into :PARTYID, :BEGINDATE
    do suspend;
end