"localhost:/firebird/data/ILICHEVSK2.FDB". ���������

GRAINTAREMOVEMENT1

������� ���������

�������� ���������

��������

(��� �������� ��� ��������� GRAINTAREMOVEMENT1)

�����������

CREATE PROCEDURE GRAINTAREMOVEMENT1(
    DATE1 DATE,
    DATE2 DATE,
    STORAGEID INTEGER,
    PARTY_ID INTEGER)
RETURNS (
    CROPCLASS VARCHAR(41),
    STORINGNAME VARCHAR(30),
    OUTCOMEBEFORE NUMERIC(15,3),
    OUTCOMEAFTER NUMERIC(15,3),
    INCOMEAFTER NUMERIC(15,3),
    INCOMEBEFORE NUMERIC(15,3),
    OUTCOME NUMERIC(15,3),
    INCOME NUMERIC(15,3),
    INCOMEAFTERNET NUMERIC(15,3),
    OUTCOMEAFTERNET NUMERIC(15,3))
AS
DECLARE VARIABLE OPERATIONID SMALLINT;
DECLARE VARIABLE NET INTEGER;
DECLARE VARIABLE REGDATE DATE;
DECLARE VARIABLE OPERATIONSUBTYPE SMALLINT;
DECLARE VARIABLE STORINGID INTEGER;
DECLARE VARIABLE CLASSID INTEGER;
DECLARE VARIABLE WEIGHT INTEGER;
BEGIN
   FOR
      select  crop.Shortcropname||' '||class.classname cropclass, Storing.storingname, Q1.classid, It.storingid
      from  IT
      left join quality Q1 on (IT.samplerqualityid = Q1.QUALITYID)
      left join operation  on (it.operationid = operation.operationid)
      Left join Crop on  (Q1.cropid = Crop.cropid)
      left join storing on (IT.Storingid = storing.STORINGID)
      inner join Class on (Q1.classid = class.classid)
     where crop.croptypeid = 1
 and it.Storageid = :storageid
 and it.regdate <= :date2 and IT.state >= 0
 and operation.operationsubtype <> 2
 and it.parentid >=0
 and it.state >=0
 and it.net <> 0 and it.partyid = :party_id
 group by crop.shortcropname, class.classname, Storing.storingname, Q1.classid, It.storingid
   INTO :cropclass,
         :STORINGNAME,
         :classid,
         :storingid
  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
      left outer join quality Q1 on (IT.samplerqualityid = Q1.QUALITYID)
      LEFT OUTER JOIN Operation op1 ON (IT.operationid = op1.operationid)
      Left outer join Crop on  (Q1.cropid = Crop.cropid)
     where it.net is not null and it.Storageid = :storageid
     and it.regdate <= :date2 and q1.classid = :classid
     and it.partyid = :party_id
     and IT.state >= 0   and op1.operationsubtype <>2
     and it.operationid <>17
     and it.parentid  >= 0
     and (it.STORINGID = :STORINGID or (:STORINGID is null and it.STORINGID is null))
     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)) 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;
    SUSPEND;
  end
END