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

GET_CONTRACT_COSTS

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

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

Описание

Возвращает цену услуги по контракту

Определение

CREATE PROCEDURE GET_CONTRACT_COSTS(
    REGDATE DATE,
    CONTRACTID INTEGER,
    SERVICEID INTEGER,
    CROPID INTEGER,
    CLASSID INTEGER)
RETURNS (
    COST DECIMAL(15,4),
    CONTRACTCOSTDATE DATE,
    PRIVILEGEDAYS INTEGER,
    WEIGHT INTEGER,
    HUMB NUMERIC(4,2),
    DIRTB NUMERIC(4,2),
    SCB NUMERIC(4,2),
    QUALITYID INTEGER)
AS
begin

  for
    select distinct CONTRACTCOSTS.COST, CONTRACTCOSTS.CONTRACTCOSTDATE,
        CONTRACTCOSTS.PRIVILEGEDAYS, CONTRACTCOSTS.WEIGHT, CONTRACTCOSTS.QUALITYID,
        QUALITY_SELECT.HUMIDITY, QUALITY_SELECT.DIRT, QUALITY_SELECT.SCREENING
      from CONTRACTCOSTS
        left join QUALITY_SELECT(CONTRACTCOSTS.QUALITYID) on (0=0)
      where (CONTRACTCOSTS.CONTRACTCOSTDATE = (select max(CCS.CONTRACTCOSTDATE)
                                                 from CONTRACTCOSTS CCS
                                                   left join QUALITY on (QUALITY.QUALITYID = CCS.QUALITYID)
                                                 where (CCS.CONTRACTID = :CONTRACTID)
                                                   and (CCS.SERVICEID = :SERVICEID)
                                                   and (CCS.CONTRACTCOSTDATE <= :REGDATE)
                                                   and ((QUALITY.CROPID = :CROPID) and (QUALITY.CLASSID = :CLASSID))
                                               group by CCS.CONTRACTID))
        and (CONTRACTCOSTS.CONTRACTID = :CONTRACTID)
        and (CONTRACTCOSTS.SERVICEID = :SERVICEID)
        and ((QUALITY_SELECT.CROPID = :CROPID) and (QUALITY_SELECT.CLASSID = :CLASSID))
    order by CONTRACTCOSTS.PRIVILEGEDAYS desc, CONTRACTCOSTS.WEIGHT desc
    into :COST, :CONTRACTCOSTDATE, :PRIVILEGEDAYS, :WEIGHT, :QUALITYID, :HUMB, :DIRTB, :SCB
  do suspend;

  if (:QUALITYID is null) then begin
    for
      select distinct CONTRACTCOSTS.COST, CONTRACTCOSTS.CONTRACTCOSTDATE,
          CONTRACTCOSTS.PRIVILEGEDAYS, CONTRACTCOSTS.WEIGHT, CONTRACTCOSTS.QUALITYID,
          QUALITY_SELECT.HUMIDITY, QUALITY_SELECT.DIRT, QUALITY_SELECT.SCREENING
        from CONTRACTCOSTS
          left join QUALITY_SELECT(CONTRACTCOSTS.QUALITYID) on (0=0)
        where (CONTRACTCOSTS.CONTRACTCOSTDATE = (select max(CCS.CONTRACTCOSTDATE)
                                                   from CONTRACTCOSTS CCS
                                                     left join QUALITY on (QUALITY.QUALITYID = CCS.QUALITYID)
                                                   where (CCS.CONTRACTID = :CONTRACTID)
                                                     and (CCS.SERVICEID = :SERVICEID)
                                                     and (CCS.CONTRACTCOSTDATE <= :REGDATE)
                                                     and ((QUALITY.CROPID = :CROPID) and (QUALITY.CLASSID is null))
                                                 group by CCS.CONTRACTID))
          and (CONTRACTCOSTS.CONTRACTID = :CONTRACTID)
          and (CONTRACTCOSTS.SERVICEID = :SERVICEID)
          and ((QUALITY_SELECT.CROPID = :CROPID) and (QUALITY_SELECT.CLASSID is null))
      order by CONTRACTCOSTS.PRIVILEGEDAYS desc, CONTRACTCOSTS.WEIGHT desc
      into :COST, :CONTRACTCOSTDATE, :PRIVILEGEDAYS, :WEIGHT, :QUALITYID, :HUMB, :DIRTB, :SCB
    do suspend;

    if (:QUALITYID is null) then begin
      for
        select distinct CONTRACTCOSTS.COST, CONTRACTCOSTS.CONTRACTCOSTDATE,
            CONTRACTCOSTS.PRIVILEGEDAYS, CONTRACTCOSTS.WEIGHT, CONTRACTCOSTS.QUALITYID,
            QUALITY_SELECT.HUMIDITY, QUALITY_SELECT.DIRT, QUALITY_SELECT.SCREENING
          from CONTRACTCOSTS
            left join QUALITY_SELECT(CONTRACTCOSTS.QUALITYID) on (0=0)
          where (CONTRACTCOSTS.CONTRACTCOSTDATE = (select max(CCS.CONTRACTCOSTDATE)
                                                     from CONTRACTCOSTS CCS
                                                       left join QUALITY on (QUALITY.QUALITYID = CCS.QUALITYID)
                                                     where (CCS.CONTRACTID = :CONTRACTID)
                                                       and (CCS.SERVICEID = :SERVICEID)
                                                       and (CCS.CONTRACTCOSTDATE <= :REGDATE)
                                                       and ((QUALITY.CROPID is null) and (QUALITY.CLASSID is null))
                                                   group by CCS.CONTRACTID))
            and (CONTRACTCOSTS.CONTRACTID = :CONTRACTID)
            and (CONTRACTCOSTS.SERVICEID = :SERVICEID)
            and ((QUALITY_SELECT.CROPID is null) and (QUALITY_SELECT.CLASSID is null))
        order by CONTRACTCOSTS.PRIVILEGEDAYS desc, CONTRACTCOSTS.WEIGHT desc
        into :COST, :CONTRACTCOSTDATE, :PRIVILEGEDAYS, :WEIGHT, :QUALITYID, :HUMB, :DIRTB, :SCB
      do suspend;

      if (:QUALITYID is null) then begin
        for
          select distinct CONTRACTCOSTS.COST, CONTRACTCOSTS.CONTRACTCOSTDATE,
              CONTRACTCOSTS.PRIVILEGEDAYS, CONTRACTCOSTS.WEIGHT, CONTRACTCOSTS.QUALITYID,
              QUALITY_SELECT.HUMIDITY, QUALITY_SELECT.DIRT, QUALITY_SELECT.SCREENING
            from CONTRACTCOSTS
              left join QUALITY_SELECT(CONTRACTCOSTS.QUALITYID) on (0=0)
            where (CONTRACTCOSTS.CONTRACTCOSTDATE = (select max(CCS.CONTRACTCOSTDATE)
                                                       from CONTRACTCOSTS CCS
                                                         left join QUALITY on (QUALITY.QUALITYID = CCS.QUALITYID)
                                                       where (CCS.CONTRACTID = -1)
                                                         and (CCS.SERVICEID = :SERVICEID)
                                                         and (CCS.CONTRACTCOSTDATE <= :REGDATE)
                                                         and ((QUALITY.CROPID = :CROPID) and (QUALITY.CLASSID = :CLASSID))
                                                     group by CCS.CONTRACTID))
              and (CONTRACTCOSTS.CONTRACTID = -1)
              and (CONTRACTCOSTS.SERVICEID = :SERVICEID)
              and ((QUALITY_SELECT.CROPID = :CROPID) and (QUALITY_SELECT.CLASSID = :CLASSID))
          order by CONTRACTCOSTS.PRIVILEGEDAYS desc, CONTRACTCOSTS.WEIGHT desc
          into :COST, :CONTRACTCOSTDATE, :PRIVILEGEDAYS, :WEIGHT, :QUALITYID, :HUMB, :DIRTB, :SCB
        do suspend;

        if (:QUALITYID is null) then begin
          for
            select distinct CONTRACTCOSTS.COST, CONTRACTCOSTS.CONTRACTCOSTDATE,
                CONTRACTCOSTS.PRIVILEGEDAYS, CONTRACTCOSTS.WEIGHT, CONTRACTCOSTS.QUALITYID,
                QUALITY_SELECT.HUMIDITY, QUALITY_SELECT.DIRT, QUALITY_SELECT.SCREENING
              from CONTRACTCOSTS
                left join QUALITY_SELECT(CONTRACTCOSTS.QUALITYID) on (0=0)
              where (CONTRACTCOSTS.CONTRACTCOSTDATE = (select max(CCS.CONTRACTCOSTDATE)
                                                         from CONTRACTCOSTS CCS
                                                           left join QUALITY on (QUALITY.QUALITYID = CCS.QUALITYID)
                                                         where (CCS.CONTRACTID = -1)
                                                           and (CCS.SERVICEID = :SERVICEID)
                                                           and (CCS.CONTRACTCOSTDATE <= :REGDATE)
                                                           and ((QUALITY.CROPID = :CROPID) and (QUALITY.CLASSID is null))
                                                       group by CCS.CONTRACTID))
                and (CONTRACTCOSTS.CONTRACTID = -1)
                and (CONTRACTCOSTS.SERVICEID = :SERVICEID)
                and ((QUALITY_SELECT.CROPID = :CROPID) and (QUALITY_SELECT.CLASSID is null))
            order by CONTRACTCOSTS.PRIVILEGEDAYS desc, CONTRACTCOSTS.WEIGHT desc
            into :COST, :CONTRACTCOSTDATE, :PRIVILEGEDAYS, :WEIGHT, :QUALITYID, :HUMB, :DIRTB, :SCB
          do suspend;

          if (:QUALITYID is null) then begin
            for
              select distinct CONTRACTCOSTS.COST, CONTRACTCOSTS.CONTRACTCOSTDATE,
                  CONTRACTCOSTS.PRIVILEGEDAYS, CONTRACTCOSTS.WEIGHT, CONTRACTCOSTS.QUALITYID,
                  QUALITY_SELECT.HUMIDITY, QUALITY_SELECT.DIRT, QUALITY_SELECT.SCREENING
                from CONTRACTCOSTS
                  left join QUALITY_SELECT(CONTRACTCOSTS.QUALITYID) on (0=0)
                where (CONTRACTCOSTS.CONTRACTCOSTDATE = (select max(CCS.CONTRACTCOSTDATE)
                                                           from CONTRACTCOSTS CCS
                                                             left join QUALITY on (QUALITY.QUALITYID = CCS.QUALITYID)
                                                           where (CCS.CONTRACTID = -1)
                                                             and (CCS.SERVICEID = :SERVICEID)
                                                             and (CCS.CONTRACTCOSTDATE <= :REGDATE)
                                                             and ((QUALITY.CROPID is null) and (QUALITY.CLASSID is null))
                                                         group by CCS.CONTRACTID))
                  and (CONTRACTCOSTS.CONTRACTID = -1)
                  and (CONTRACTCOSTS.SERVICEID = :SERVICEID)
                  and ((QUALITY_SELECT.CROPID is null) and (QUALITY_SELECT.CLASSID is null))
              order by CONTRACTCOSTS.PRIVILEGEDAYS desc, CONTRACTCOSTS.WEIGHT desc
              into :COST, :CONTRACTCOSTDATE, :PRIVILEGEDAYS, :WEIGHT, :QUALITYID, :HUMB, :DIRTB, :SCB
            do suspend;
          end
        end
      end
    end
end

end