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

GRAINTAREMOVEMENT2

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

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

��������

(��� �������� ��� ��������� GRAINTAREMOVEMENT2)

�����������

CREATE PROCEDURE GRAINTAREMOVEMENT2(
    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),
    WOFFS NUMERIC(15,3),
    OUTCOME NUMERIC(15,3),
    INCOME 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;
BEGIN
--  select partyid from parties where state =1 into :party_id;
  FOR
    select  crop.Shortcropname||' '||class.classname cropclass, Storing.storingname, Quality.classid, It.storingid
      from  IT
      left outer join quality on (IT.LABQUALITYID = Quality.QUALITYID)
      LEFT OUTER JOIN Operation ON (IT.operationid = operation.operationid)
      Left outer join Crop on  (Quality.cropid = Crop.cropid)
      Left outer join Class on (Quality.classid = class.classid)
      inner join Storing on (IT.Storingid = Storing.storingid)
     where crop.croptypeid = 3 and It.partyid = :party_id and it.state >= 0
     and it.regdate <= :date2
     And It.storageid = :storageid and operation.operationsubtype IN (0,1)
    group by crop.shortcropname, class.classname, Storing.storingname, Quality.classid, It.storingid
    INTO :cropclass,
         :STORINGNAME,
         :classid,
         :storingid
  DO
  BEGIN
     OUTCOMEBEFORE =0;
     OUTCOMEAFTER = 0;
     INCOMEAFTER = 0;
     INCOMEBEFORE =0;
     OUTCOME =0;
     WOFFS =0;
     INCOME = 0;
  FOR
    select IT.net, operation.operationsubtype, IT.regdate, IT.operationid
      from  IT
      left outer join quality on (IT.LABQUALITYID = Quality.QUALITYID)
      LEFT OUTER JOIN Operation ON (IT.operationid = operation.operationid)
      Left outer join Crop on  (Quality.cropid = Crop.cropid)
     where crop.croptypeid = 3 and It.partyid = :party_id and it.state >= 0
     and it.regdate <= :date2
     and quality.classid = :classid and it.storingid = :storingid
     And It.storageid = :storageid and operation.operationsubtype IN (0,1)
     into
      :NET,
      :operationsubtype,
      :regdate,
      :operationid
   do
   begin
   if (:operationsubtype = 1 and :regdate < :date1 ) then
     OUTCOMEBEFORE = :OUTCOMEBEFORE + :Net;
   if (:operationsubtype = 0 and :regdate < :date1 ) then
     INCOMEBEFORE = :INCOMEBEFORE + :Net;
   if (:operationsubtype = 1 and :regdate <= :date2 ) then
     OUTCOMEAFTER = :outcomeafter + :Net;
   if (:operationsubtype = 0 and :regdate <= :date2 ) then
     INCOMEAFTER = :INCOMEAFTER + :Net;
   if (:operationsubtype = 1 and :regdate between :date1 and :date2 ) then
     OUTCOME = :OUTCOME + :Net;
   if (:operationsubtype = 0 and :regdate between :date1 and :date2 ) then
     INCOME = :INCOME + :Net;
   if (:operationid = 13 and :regdate between :date1 and :date2 ) then
     woffs = :woffs + :Net;
   end
     OUTCOMEBEFORE = :OUTCOMEBEFORE/1000;
     OUTCOMEAFTER = :OUTCOMEAFTER/1000;
     INCOMEAFTER = :INCOMEAFTER/1000;
     INCOMEBEFORE = :INCOMEBEFORE/1000;
     OUTCOME = OUTCOME/1000;
     WOFFS = WOFFS/1000;
     INCOME = INCOME/1000;
    SUSPEND;
    END
END