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

FORAGE_REGISTER

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

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

Описание

(Нет описания для процедуры FORAGE_REGISTER)

Определение

CREATE PROCEDURE FORAGE_REGISTER(
    BEGDATE DATE,
    ENDDATE DATE)
RETURNS (
    CROPNAME VARCHAR(20),
    UNDERWORK INTEGER,
    COOLING INTEGER,
    OUT INTEGER,
    MOVE INTEGER,
    INAVTO INTEGER,
    INRRW INTEGER,
    DRY INTEGER,
    PERCENT NUMERIC(15,2),
    WEIGHT INTEGER)
AS
DECLARE VARIABLE FORAGE_WEIGHT_FULL INTEGER;
DECLARE VARIABLE MOVE_WEIGHT_FULL INTEGER;
DECLARE VARIABLE CROPID INTEGER;
DECLARE VARIABLE OPERATIONID INTEGER;
DECLARE VARIABLE NET INTEGER;
DECLARE VARIABLE TRANSPORTTYPE INTEGER;
DECLARE VARIABLE ACROPNAME VARCHAR(20);
DECLARE VARIABLE OLD_CROPID INTEGER;
DECLARE VARIABLE INVOICEID INTEGER;
begin /*$$IB$$
  select sum(it.net)
  from it
    left join quality on it.samplerqualityid = quality.qualityid
  where it.regdate between :begdate and :enddate
    and it.operationid = 5
    and it.state > -1
    and it.Storageid = 1
    and it.parentid <= 0
    and quality.classid = 41
  into :FORAGE_WEIGHT_FULL;

  select sum(it.net)
  from it
    left join quality on it.samplerqualityid = quality.qualityid
  where it.regdate between :begdate and :enddate
    and it.operationid in (29, 12, 5, 1, 31)
    and it.state > -1
    and it.Storageid = 1
    and it.parentid <= 0
    and quality.cropid is not null
  into :MOVE_WEIGHT_FULL;

  OLD_CROPID = -1;
  for
   select quality.cropid, it.operationid, sum(it.net), min(crop.cropname),
      case it.operationid
      when 1 then transporttype.transportsubtype
      else null
      end transportsubtype,
      case
      when uwoutacts.invoiceid is null then null
      else 1
      end AInvoiceID
    from it
      left join quality on it.samplerqualityid = quality.qualityid
      left join transporttype on it.transporttypeid = transporttype.transporttypeid
      left join crop on quality.cropid = crop.cropid
      left join uwoutacts on uwoutacts.invoiceid = it.invoiceid
    where it.regdate between :begdate and :enddate
      and it.operationid in (29, 12, 5, 1, 31)
      and it.state > -1
      and it.Storageid = 1
      and it.parentid <= 0
    group by quality.cropid, it.operationid,
      case it.operationid
      when 1 then transporttype.transportsubtype
      else null
      end,
      case
      when uwoutacts.invoiceid is null then null
      else 1
      end
    order by 1
    into :CROPID, :OPERATIONID, :NET, :ACROPNAME, :TRANSPORTTYPE, :INVOICEID
  do begin
    if (:OLD_CROPID <> :CROPID) then begin
      if (:OLD_CROPID <> -1) then begin
        weight = cast(cast((coalesce(:inavto, 0) + coalesce(:underwork, 0) + coalesce(:inrrw, 0) + coalesce(:out,  0) + coalesce(:cooling,  0) + coalesce(:move,  0) + coalesce(:dry,  0)) * :FORAGE_WEIGHT_FULL as double precision) / :MOVE_WEIGHT_FULL as integer);
        percent = cast(cast((coalesce(:inavto, 0) + coalesce(:underwork, 0) + coalesce(:inrrw, 0) + coalesce(:out,  0) + coalesce(:cooling,  0) + coalesce(:move,  0) + coalesce(:dry,  0)) * 100 as double precision) / :MOVE_WEIGHT_FULL as numeric(9,2));
        suspend;
        inavto = null;
        inrrw = null;
        out = null;
        cooling = null;
        move = null;
        dry = null;
        underwork = null;
      end
      OLD_CROPID = :CROPID;
      cropname = :ACROPNAME;
    end
    if (:OPERATIONID = 1 and :TRANSPORTTYPE = 0) then inavto = :NET;
    if (:OPERATIONID = 1 and :TRANSPORTTYPE = 1) then inrrw = :NET;
    if (:OPERATIONID = 5) then out = :NET;
    if (:OPERATIONID = 12) then cooling = :NET;
    if (:OPERATIONID = 29 and :INVOICEID is null) then move = :NET;
    if (:OPERATIONID = 29 and :INVOICEID is not null) then underwork = :NET;
    if (:OPERATIONID = 31) then dry = :NET;
  end
  if (:OLD_CROPID <> -1) then begin
    weight = cast(cast((coalesce(:inavto, 0) + coalesce(:underwork, 0) + coalesce(:inrrw, 0) + coalesce(:out,  0) + coalesce(:cooling,  0) + coalesce(:move,  0) + coalesce(:dry,  0)) * :FORAGE_WEIGHT_FULL as double precision) / :MOVE_WEIGHT_FULL as integer);
    percent = cast(cast((coalesce(:inavto, 0) + coalesce(:underwork, 0) + coalesce(:inrrw, 0) + coalesce(:out,  0) + coalesce(:cooling,  0) + coalesce(:move,  0) + coalesce(:dry,  0)) * 100 as double precision) / :MOVE_WEIGHT_FULL as numeric(9,2));
    suspend;
  end
 $$IB$$*/ EXIT;
end