"localhost:/firebird/data/ILICHEVSK2.FDB". Процедуры Previous topic Chapter index Next topic

FORM371

 

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

Параметр

Тип

Описание

DATE1

DATE

 

DATE2

DATE

 

STORAGEID

INTEGER

 

PARTYID

INTEGER

 

STORINGID

INTEGER

 

PARTYDATE

DATE

 

 

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

Параметр

Тип

Описание

OUTCOMEBEFORE

NUMERIC(15,3)

 

SHORTCROPNAME

VARCHAR(20)

 

CLASSNAME

VARCHAR(20)

 

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)

 

SUPLIERNAME

VARCHAR(30)

 

 

Описание

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

 

Определение

CREATE PROCEDURE FORM371(
    DATE1 DATE,
    DATE2 DATE,
    STORAGEID INTEGER,
    PARTYID INTEGER,
    STORINGID INTEGER,
    PARTYDATE DATE)
RETURNS (
    OUTCOMEBEFORE NUMERIC(15,3),
    SHORTCROPNAME VARCHAR(20),
    CLASSNAME VARCHAR(20),
    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),
    SUPLIERNAME VARCHAR(30))
AS
declare variable OPERATIONID integer;
declare variable REGDATE date;
declare variable OPERATIONSUBTYPE smallint;
declare variable WEIGHT integer;
declare variable NET integer;
declare variable CLASSID integer;
declare variable OWNERID integer;
BEGIN
FOR
  select
    crop.shortcropname,
    class.classname,
    q1.classid,
    clients.clientname,
    it.ownerid
  from  IT
    join GetWorkParties(:PARTYID,:PARTYDATE, null,null,:storageID)
      on (IT.PartyID = GetWorkParties.PartyID)
    left join quality Q1 on (IT.samplerqualityid = Q1.QUALITYID)
    inner join clients on (it.ownerid = clients.clientid)
    left join operation  on (it.operationid = operation.operationid)
    Left join Crop on  (Q1.cropid = Crop.cropid)
    inner join Class on (Q1.classid = class.classid)
  where it.regdate <= :date2
    and it.net is not null
    and it.Storageid = :storageid
    and (IT.Storingid  = :storingid)
    and crop.croptypeid <> 2
    and IT.state >= 0
    and operation.operationsubtype <> 2
    and it.parentid >= 0
    and it.state >=0
  group by
    crop.shortcropname,
    class.classname,
    q1.classid,
    clients.clientname,
    it.ownerid
  INTO :shortcropname,
       :CLASSNAME,
       :CLASSID,
       :supliername,
       :ownerid
  DO
  begin
    OUTCOMEBEFORE = 0;
    OUTCOMEAFTER = 0;
    INCOMEAFTER = 0;
    INCOMEBEFORE = 0;
    OUTCOME = 0;
    INCOME = 0;
    incomeafternet = 0;
    OUTCOMEAFTERNet = 0;
    for
      select
        it.net,
        it.weight,
        op1.operationsubtype,
        IT.regdate,
        it.operationid
      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 Operation op1 ON (IT.operationid = op1.operationid)
        Left join Crop on  (Q1.cropid = Crop.cropid)
      where it.regdate <= :date2
        and it.net is not null
        and it.Storageid = :storageid
        and (IT.Storingid  = :storingid)
        and q1.classid = :classid
        and IT.state >= 0
        and op1.operationsubtype in (0,1)
        and it.parentid >= 0
        and it.ownerid = :ownerid
     into :NET,
       :weight,
       :OPERATIONSUBTYPE,
       :regdate,
       :operationid
   do
   begin
     if ((:regdate < :date1) and (:operationsubtype = 1)) then
     begin
       if (:operationid in (9,13,24,25)) then
         OUTCOMEBEFORE = :outcomebefore + :weight*-1;
       else
         OUTCOMEBEFORE = :outcomebefore + :weight;
     end
     if ((:regdate <= :date2) and (:operationsubtype = 1)) then
     begin
     if (:operationid in (9,13,24,25)) 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 (:operationid in (9,13,24,25)) then
         OUTCOME = :OUTCOME + :weight*-1;
       else
         OUTCOME = :OUTCOME + :weight;
     end
     if ((:regdate between :date1 and :date2) and (:operationsubtype = 0) and (:operationid <> 17)) then
      INCOME = :INCOME + :net;
     if ((:regdate between :date1 and :date2) and (:operationid = 17)) then
     begin
       if (:net > 0) then
        INCOME = :INCOME + :net;
       else
        OUTCOME = :OUTCOME + :net * -1;
     end

     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

 

     Previous topic Chapter index Next topic