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

FORM6

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

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

Описание

Форма 6

Определение

CREATE PROCEDURE FORM6(
    DATE1 DATE,
    DATE2 DATE,
    PARTYID INTEGER,
    PARTYDATE DATE)
RETURNS (
    SUPLIERNAME VARCHAR(100),
    CROPCLASS VARCHAR(41),
    AVTOINCOME NUMERIC(15,3),
    INCOME NUMERIC(15,3),
    INVENTINCOME NUMERIC(15,3),
    PODRINCOME NUMERIC(15,3),
    ZDINCOME NUMERIC(15,3),
    REINCOME NUMERIC(15,3),
    AVTOUTCOME NUMERIC(15,3),
    ZDOUTCOME NUMERIC(15,3),
    KZEXOUTCOME NUMERIC(15,3),
    M2 NUMERIC(15,3),
    M1 NUMERIC(15,3),
    K3OUTCOME NUMERIC(15,3),
    REOUTCOME NUMERIC(15,3),
    K2OUTCOME NUMERIC(15,3),
    INCOMEBEFORE NUMERIC(15,3),
    OUTCOME NUMERIC(15,3),
    OUTCOMEBEFORE NUMERIC(15,3),
    M3 NUMERIC(15,3),
    WOFF NUMERIC(15,3),
    INVENTOUTCOME NUMERIC(15,3))
AS
declare variable classid integer;
declare variable suplierid integer;
declare variable transporttypeid integer;
declare variable operationtype integer;
declare variable net integer;
declare variable operationsubtype integer;
declare variable regdate date;
declare variable storageid integer;
declare variable operationid integer;
BEGIN
  for
    select
      clients.clientfullname,
      qnames.cropfullname as CROPCLASS,
      it.ownerid,
      class.classid
    FROM IT
      join GetWorkParties(:PARTYID,:PARTYDATE, null,null,null)
       on (IT.PartyID = GetWorkParties.PartyID)
      left join quality on (it.BuhQualityid = quality.qualityid)
      left join Operation ON (IT.operationid = operation.operationid)
      left join quality_cropfullname(quality.cropid, quality.classid, null, 1) qnames on (0 = 0)
      left join CLASS ON (QUALITY.CLASSID = CLASS.CLASSID)
      left join CROP ON (QUALITY.CROPID = CROP.CROPID)
      left join STORAGE ON (IT.STORAGEID = STORAGE.STORAGEID)
      left join clients  on (IT.Ownerid = clients.clientid)
    where Storagetypeid = 1
      and it.regdate <= :date2
      and it.weight is not null
      and operation.operationsubtype IN (0,1)
      and It.BuhQualityid is not null
      and IT.state >= 0
      and parentid >= 0
    Group by
     qnames.cropfullname,
      clients.clientfullname,
      Class.Classid,
      it.ownerid
    INTO :SUPLIERNAME,
         :CROPCLASS,
         :SUPLIERID,
         :CLASSID
  do
  begin
    ZDINCOME = 0;
    AVTOINCOME = 0;
    PODRINCOME = 0;
    INVENTINCOME = 0;
    INCOME = 0;
    M1 = 0;
    M2 = 0;
    M3 = 0;
    WOFF = 0;
    KZEXOUTCOME = 0;
    ZDOUTCOME = 0;
    AVTOUTCOME = 0;
    REOUTCOME = 0;
    K3OUTCOME = 0;
    K2OUTCOME = 0;
    OUTCOME = 0;
    INCOMEBEFORE = 0;
    OUTCOMEBEFORE = 0;
    REINCOME = 0;
  for
    SELECT
      coalesce(IT.weight,0),
      it.operationid,
      operation.operationtype,
      IT.regdate,
      ittransport.transporttypeid,
      operation.operationsubtype,
      it.providerid
    FROM IT
      join GetWorkParties(:PARTYID,:PARTYDATE, null,null,null)
        on (IT.PartyID = GetWorkParties.PartyID)
      left join quality on (it.BuhQualityid = quality.qualityid)
      LEFT JOIN Operation ON (IT.operationid = operation.operationid)
      left join ittransport on (it.inventoryid = ittransport.inventoryid)
    where it.regdate <= :date2
      and it.weight is not null
      and operation.operationsubtype IN (0,1)
      and It.BuhQualityid is not null
      and it.ownerid = :suplierid
      AND QUALITY.classid = :classid
      and IT.state >=0
      and parentid >= 0
    INTO   :NET,
      :OPERATIONID,
      :OPERATIONTYPE,
      :REGDATE,
      :TRANSPORTTYPEID,
      :OPERATIONSUBTYPE,
      :storageid
    DO
    BEGIN
      if  ((:operationsubtype = 0) and (:REGDATE between :date1 and :date2)) then
        income = :income + :net;
      else if  ((:operationsubtype = 0) and (:REGDATE < :date1)) then
        incomebefore = :incomebefore + :net;
      else if  ((:operationsubtype = 1) and (:REGDATE between :date1 and :date2)) then
        outcome = :outcome + :net;
      else if  ((:operationsubtype = 1) and (:REGDATE < :date1)) then
       outcomebefore = :outcomebefore + :net;

      if ((:OPERATIONTYPE = 6) and (:REGDATE between :date1 and :date2)) then
      begin
        if (:net > 0) then
          reincome = :reincome + :net;
        else
          reoutcome = :reoutcome + :net*-1;
      end
-- что сюда попадпет???
      else if ((:OPERATIONID in (3,4)) and (:REGDATE between :date1 and :date2)) then
            podrincome = :podrincome + :net;
--
      else if ((:operationid = 18) and (:REGDATE between :date1 and :date2)) then
       inventincome = :inventincome + :net;

      else if ((:operationid = 7) and (:storageid = 39) and (:REGDATE between :date1 and :date2)) then
        m1 = :m1 + :net;
      else if ((:operationid = 7) and (:storageid = 49) and (:REGDATE between :date1 and :date2)) then
       m2 = :m2 + :net;
      else if ((:operationid = 7) and (:storageid = 222) and (:REGDATE between :date1 and :date2)) then
       m3 = :m3 + :net;
---
      else if ((:operationid = 19) and (:REGDATE between :date1 and :date2)) then
        inventoutcome = :inventoutcome + :net;

      if ((:operationid = 30) and (:REGDATE between :date1 and :date2)) then
        kzexoutcome = :kzexoutcome + :net;

      if ((:transporttypeid = 2) and (:REGDATE between :date1 and :date2)) then
      begin
        if (:operationsubtype = 0) then
          zdincome = :zdincome + :net;
        else
         zdoutcome = :zdoutcome + :net;
      end

      if ((:transporttypeid in (0,1)) and (:operationsubtype = 1) and (:REGDATE between :date1 and :date2)) then
      begin
        if (:operationsubtype = 0) then
          avtoutcome = :avtoutcome + :net;
        else
         avtoincome = :avtoincome + :net;
      end

--   if  ((:net < 0 ) and (:operationid = 17) and (:REGDATE between :date1 and :date2)) then
--     reoutcome = :reoutcome + :net*-1;
     if  ((:OPERATIONTYPE = 5) and (:REGDATE between :date1 and :date2)) then
       k3outcome = :k3outcome + :net;
     if  ((:OPERATIONTYPE = 1) and (:REGDATE between :date1 and :date2)) then
       k2outcome = :k2outcome + :net;
     if ((:OPERATIONTYPE = 2) and (:REGDATE between :date1 and :date2) ) then
       woff = :woff + :net * -1;

     ZDINCOME = coalesce(:ZDINCOME,0)/1000;
     AVTOINCOME = coalesce(:AVToINCOME,0)/1000;
     PODRINCOME = coalesce(:PODRINCOME,0)/1000;
     INVENTINCOME = coalesce(:INVENTINCOME,0)/1000;
     INVENTOUTCOME = coalesce(:INVENTOUTCOME,0)/1000;
     INCOME = coalesce(:INCOME,0)/1000;
     M1 = coalesce(:M1,0)/1000;
     M2 = coalesce(:M2,0)/1000;
     M3 = coalesce(:M3,0)/1000;
     Woff = coalesce(:woff,0)/1000;
     KZEXOUTCOME = coalesce(:KZEXOUTCOME,0)/1000;
     ZDOUTCOME = coalesce(:ZDOUTCOME,0)/1000;
     AVTOUTCOME = coalesce(:AVTOUTCOME,0)/1000;
     REOUTCOME = coalesce(:REOUTCOME,0)/1000;
     K3OUTCOME = coalesce(:K3OUTCOME,0)/1000;
     K2OUTCOME = coalesce(:K2OUTCOME,0)/1000;
     OUTCOME = coalesce(:OUTCOME,0)/1000;
     INCOMEBEFORE = coalesce(:INCOMEBEFORE,0)/1000;
     OUTCOMEBEFORE = coalesce(:OUTCOMEBEFORE,0)/1000;
     REINCOME = coalesce(:REINCOME,0)/1000;
   END
   SUSPEND;
  end
END