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

GET_PARTYID

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

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

Описание

Возвращает ID партий с учетом года урожая

Определение

CREATE PROCEDURE GET_PARTYID(
    ISCURRENT INTEGER,
    PARTYDATE DATE,
    CROPID INTEGER,
    CLASSID INTEGER,
    STORAGEID INTEGER,
    HARVESTINGYEAR INTEGER)
RETURNS (
    PARTYID INTEGER)
AS
declare variable PARTIES_ENDDATE date;
begin
  if (:PARTYDATE > current_date) then
    PARTIES_ENDDATE = :PARTYDATE;
  else PARTIES_ENDDATE = current_date;

  if (:ISCURRENT = 1) then begin
    select PARTIES.PARTYID
      from PARTIES
      where (PARTIES.STATE = 1)
        and ((PARTIES.STORAGEID = :STORAGEID)
             and (PARTIES.CROPID = :CROPID)
             and (PARTIES.CLASSID = :CLASSID)
             and (PARTIES.HARVESTINGYEAR = :HARVESTINGYEAR))
    into:PARTYID;

    if (:PARTYID is null) then
      select PARTIES.PARTYID
        from PARTIES
        where (PARTIES.STATE = 1)
          and ((PARTIES.STORAGEID = :STORAGEID)
               and (PARTIES.CROPID = :CROPID)
               and (PARTIES.CLASSID is null)
               and (PARTIES.HARVESTINGYEAR = :HARVESTINGYEAR))
      into :PARTYID;

    if (:PARTYID is null) then
      select PARTIES.PARTYID
        from PARTIES
        where (PARTIES.STATE = 1)
          and ((PARTIES.STORAGEID = :STORAGEID)
               and (PARTIES.CROPID = :CROPID)
               and (PARTIES.CLASSID = :CLASSID)
               and (PARTIES.HARVESTINGYEAR is null))
      into :PARTYID;

    if (:PARTYID is null) then
      select PARTIES.PARTYID
        from PARTIES
        where (PARTIES.STATE = 1)
          and ((PARTIES.STORAGEID = :STORAGEID)
               and (PARTIES.CROPID = :CROPID)
               and (PARTIES.CLASSID is null)
               and (PARTIES.HARVESTINGYEAR is null))
      into :PARTYID;
  end
  else begin
    select PARTIES.PARTYID
      from PARTIES
      where (:PARTYDATE between PARTIES.BEGINDATE and coalesce(PARTIES.ENDDATE,:PARTIES_ENDDATE))
        and ((PARTIES.STORAGEID = :STORAGEID)
             and (PARTIES.CROPID = :CROPID)
             and (PARTIES.CLASSID = :CLASSID)
             and (PARTIES.HARVESTINGYEAR = :HARVESTINGYEAR))
    into :PARTYID;

    if (:PARTYID is null) then
      select PARTIES.PARTYID
        from PARTIES
        where (:PARTYDATE between PARTIES.BEGINDATE and coalesce(PARTIES.ENDDATE,:PARTIES_ENDDATE))
          and ((PARTIES.STORAGEID = :STORAGEID)
               and (PARTIES.CROPID = :CROPID)
               and (PARTIES.CLASSID is null)
               and (PARTIES.HARVESTINGYEAR = :HARVESTINGYEAR))
      into :PARTYID;

    if (:PARTYID is null) then
      select PARTIES.PARTYID
        from PARTIES
        where (:PARTYDATE between PARTIES.BEGINDATE and coalesce(PARTIES.ENDDATE,:PARTIES_ENDDATE))
          and ((PARTIES.STORAGEID = :STORAGEID)
               and (PARTIES.CROPID = :CROPID)
             and (PARTIES.CLASSID = :CLASSID)
               and (PARTIES.HARVESTINGYEAR is null))
     into :PARTYID;

    if (:PARTYID is null) then
      select PARTIES.PARTYID
        from PARTIES
        where (:PARTYDATE between PARTIES.BEGINDATE and coalesce(PARTIES.ENDDATE,:PARTIES_ENDDATE))
          and ((PARTIES.STORAGEID = :STORAGEID)
               and (PARTIES.CROPID = :CROPID)
               and (PARTIES.CLASSID is null)
               and (PARTIES.HARVESTINGYEAR is null))
      into :PARTYID;
  end

  if (:PARTYID is null) then
    PARTYID = -1;

  suspend;
end