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

FORM37

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

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

Описание

Форма 37 общая

Определение

CREATE PROCEDURE FORM37(
    DATE1 DATE,
    DATE2 DATE,
    STORAGEID SMALLINT,
    STORING SMALLINT,
    PARTYID INTEGER,
    DESKORBUH INTEGER,
    PARTYDATE DATE)
RETURNS (
    STORINGNAME VARCHAR(35),
    CROPNAME VARCHAR(20),
    CLASSNAME VARCHAR(20),
    HARVESTYEAR VARCHAR(5),
    OUTCOMEBEFORE NUMERIC(15,3),
    OUTCOMEAFTERNET NUMERIC(15,3),
    OUTCOMEAFTER NUMERIC(15,3),
    INCOMEAFTER NUMERIC(15,3),
    INCOMEAFTERNET NUMERIC(15,3),
    INCOMEBEFORE NUMERIC(15,3),
    OUTCOME NUMERIC(15,3),
    INCOME NUMERIC(15,3))
AS
DECLARE VARIABLE REGDATE DATE;
DECLARE VARIABLE OPERATIONSUBTYPE SMALLINT;
DECLARE VARIABLE WEIGHT INTEGER;
DECLARE VARIABLE NET INTEGER;
DECLARE VARIABLE CLASSID INTEGER;
DECLARE VARIABLE STORINGID INTEGER;
DECLARE VARIABLE OPERATIONTYPE INTEGER;
BEGIN
FOR     
  select
    crop.cropname,
    class.classname,
    q1.classid,
    case (:DESKORBUH) when 2 then coalesce(cast(q2.harvestyear as varchar(5)),'') else '' end
  from  IT
    join GetWorkParties(:PARTYID,:PARTYDATE, null,null,:storageid)
      on (IT.PartyID = GetWorkParties.PartyID)
    left join quality Q1 on (IT.samplerqualityid = Q1.QUALITYID)
    left join quality_select(IT.samplerqualityid) Q2 on (0=0)
    inner join operation  on (it.operationid = operation.operationid)
    left join Crop on  (Q1.cropid = Crop.cropid)
    inner join Class on (Q1.classid = class.classid)
    left join cropsort on (Q1.sortid = cropsort.sortid)
    left join storing on (it.storingid = storing.storingid)
  where it.regdate <= :date2
    and crop.croptypeid not in (2,3)
    and it.net is not null
    and it.Storageid = :storageid
    and ((IT.Storingid <> 1 or IT.Storingid is null) or (:storing = 1))
    and IT.state >= 0
    and operation.operationsubtype <> 2
    and it.parentid >= 0
    and it.state >=0
    and ((it.net is not null) or (it.weight is not null))
  group by
    crop.cropname,
    class.classname,
    q1.classid,
    case (:DESKORBUH) when 2 then coalesce(cast(q2.harvestyear as varchar(5)),'') else '' end
  INTO :CROPNAME,
       :CLASSNAME,
       :CLASSID,
       :HARVESTYEAR
  DO
  begin
    OUTCOMEBEFORE = 0;
    OUTCOMEAFTER = 0;
    INCOMEAFTER = 0;
    INCOMEBEFORE = 0;
    OUTCOME = 0;
    INCOME = 0;
    incomeafternet = 0;
    OUTCOMEAFTERNet = 0;
    for
      select
        coalesce(it.net, 0),
        coalesce(it.weight,0),
        op1.operationsubtype,
        IT.regdate,
        op1.operationtype
      from  IT
        join GetWorkParties(:PARTYID,:PARTYDATE, null,null,:storageid)
          on (IT.PartyID = GetWorkParties.PartyID)
        left join quality Q1 on (IT.samplerqualityid = Q1.QUALITYID)
        left join quality_select(IT.samplerqualityid) Q2 on (0=0)
        LEFT JOIN Operation op1 ON (IT.operationid = op1.operationid)
        Left join Crop on  (Q1.cropid = Crop.cropid)
        left join cropsort on (Q1.sortid = cropsort.sortid)
      where it.regdate <= :date2
        and it.net is not null
        and it.Storageid = :storageid
        and crop.croptypeid not in (2,3)
        and ((IT.Storingid <> 1 or IT.Storingid is null) or (:storing = 1))
        and q1.classid = :classid
        and IT.state >= 0 and op1.operationsubtype <> 2
        and it.parentid >= 0
        and ((it.net is not null) or (it.weight is not null))
        and ((coalesce(cast(q2.harvestyear as varchar(5)),'') = :HARVESTYEAR) or (:deskorbuh <> 2))
      into :NET,
        :weight,
        :OPERATIONSUBTYPE,
        :regdate,
        :OPERATIONTYPE
    do
    begin
      if ((:regdate < :date1) and (:operationsubtype = 1)) then
      begin
        if (:OPERATIONTYPE =2) then
          OUTCOMEBEFORE = :outcomebefore + :weight*-1;
        else
          OUTCOMEBEFORE = :outcomebefore + :weight;
      end

      if ((:regdate <= :date2) and (:operationsubtype = 1)) then
      begin
        if (:OPERATIONTYPE = 2) then
          OUTCOMEAFTER = :OUTCOMEAFTER + :weight*-1;
        else
          OUTCOMEAFTER = :OUTCOMEAFTER + :weight;
        OUTCOMEAFTERnet = :OUTCOMEAFTERnet + net;
      end

      if ((:regdate <= :date2) and (:operationsubtype = 0)) then
      begin
        INCOMEAFTER = :INCOMEAFTER + :weight;
        incomeafternet = :incomeafternet + :net;
      end

      if ((:regdate < :date1) and (:operationsubtype = 0)) then
        INCOMEBEFORE = :INCOMEBEFORE + :weight;

      if ((:regdate between :date1 and :date2) and (:operationsubtype = 1)) then
      begin
        if  (:OPERATIONTYPE = 2) then
          OUTCOME = :OUTCOME + :weight*-1;
        else
          OUTCOME = :OUTCOME + :weight;
      end

      if ((:regdate between :date1 and :date2) and (:operationsubtype = 0)) then
       INCOME = :INCOME + :weight;
    end

    OUTCOMEBEFORE = OUTCOMEBEFORE/1000;
    OUTCOMEAFTER =  OUTCOMEAFTER/1000;
    INCOMEAFTER = INCOMEAFTER/1000;
    INCOMEBEFORE = INCOMEBEFORE/1000;
    OUTCOME = OUTCOME/1000;
    incomeafternet = incomeafternet/1000;
    INCOME = INCOME/1000;
    OUTCOMEAFTERnet = OUTCOMEAFTERnet/1000;

    if (((:INCOMEBEFORE - :OUTCOMEBEFORE) <> 0) or
      (:income <> 0) or
      (:outcome <> 0) or
      ((:incomeafternet - :outcomeafternet - :INCOMEAFTER - :OUTCOMEAFTER) <> 0) or
      ((:INCOMEafterNET - :INCOMEafterNET) <> 0)) then
      SUSPEND;
  end
END