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

ACC_FORM37B_CLIENT

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

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

Описание

Бухгалтерия - Форма 37-Б Сводная

Определение

CREATE PROCEDURE ACC_FORM37B_CLIENT(
    DATE1 DATE,
    DATE2 DATE,
    STORAGEID INTEGER,
    CLIENTID INTEGER,
    PARTYID INTEGER,
    PARTYDATE DATE)
RETURNS (
    INITIALREM INTEGER,
    INCOME INTEGER,
    INVENTINCOME INTEGER,
    INCOMEREB INTEGER,
    OUTCOME INTEGER,
    WASTE3K INTEGER,
    SAMPLES INTEGER,
    OUTCOMEREB INTEGER,
    AFTERREM INTEGER,
    PREWASTE INTEGER,
    CLIENTNAME VARCHAR(50),
    CROPNAME VARCHAR(20),
    CLASSNAME VARCHAR(20))
AS
DECLARE VARIABLE operationsubtype SMALLINT;
DECLARE VARIABLE weight INTEGER;
DECLARE VARIABLE operationtype INTEGER;
DECLARE VARIABLE temp INTEGER;
DECLARE VARIABLE operationid INTEGER;
DECLARE VARIABLE ownerid INTEGER;
DECLARE VARIABLE classid INTEGER;
DECLARE VARIABLE weight_pre INTEGER;
DECLARE VARIABLE wclassid SMALLINT;
BEGIN
    initialrem = 0;
    afterrem = 0;
    income = 0;
    inventincome = 0;
    incomereb = 0;
    outcome = 0;
    waste3k = 0;
    samples = 0;
    outcomereb = 0;
    prewaste = 0;

  for
    select
      clients.clientname,
      it.ownerid
    from  it
      join getworkparties(:partyid,:partydate,null,null,null)
       on (it.partyid = getworkparties.partyid)
      left join clients on (it.ownerid = clients.clientid)
      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)
    where it.net is not null
      and it.regdate <= :date2
      and ((it.ownerid = :clientid) or (:clientid = 0))
      and crop.croptypeid <> 3
      and it.state >= 0
      and ((it.storageid = :storageid) or (:storageid = 0))
      and it.parentid >= 0
      and operation.operationsubtype in (0,1)
    group by clients.clientname, it.ownerid
    order by clients.clientname
     into
     :clientname,
     :ownerid
  do
  begin
    for
      select
        crop.cropname,
        class.classname,
        q1.classid
      from  it
        join getworkparties(:partyid,:partydate,null,null,null)
         on (it.partyid = getworkparties.partyid)
        left join quality q1 on (it.samplerqualityid = q1.qualityid)
        LEFT JOIN operation  ON (it.operationid = operation.operationid)
        left join quality_cropfullname(q1.cropid, q1.classid, null, 0) qn on (0 = 0)
        left join crop on (q1.cropid = crop.cropid)
        left join class on (q1.classid = class.classid)
      where it.net is not null
        and it.regdate <= :date2
        and it.ownerid = :ownerid
        and it.state >= 0
        and it.parentid >= 0
        and crop.croptypeid <> 3
        and ((it.storageid = :storageid) or (:storageid = 0))
        and operation.operationsubtype in (0,1)
      group by crop.cropname, class.classname, q1.classid
      order by crop.cropname, class.classname
      into :cropname,
        :classname,
        :classid

    do
    begin
      select
        coalesce(SUM(it.net), 0)
      from  it
        join getworkparties(:partyid,:partydate,null,null,null)
          on (it.partyid = getworkparties.partyid)
        left join quality q1 on (it.samplerqualityid = q1.qualityid)
        LEFT JOIN operation  ON (it.operationid = operation.operationid)
      where it.net is not null
        and it.regdate < :date1
        and q1.classid = :classid
        and it.ownerid = :ownerid
        and it.state >= 0
        and ((it.storageid = :storageid) or (:storageid = 0))
        and it.parentid >= 0
        and operation.operationsubtype = 0
      into :initialrem;

      select
        coalesce(SUM(it.net), 0)
      from  it
        join getworkparties(:partyid,:partydate,null,null,null)
          on (it.partyid = getworkparties.partyid)
        left join quality q1 on (it.samplerqualityid = q1.qualityid)
        LEFT JOIN operation op1 ON (it.operationid = op1.operationid)
      where it.net is not null
        and it.regdate < :date1
        and q1.classid = :classid
        and it.ownerid = :ownerid
        and it.state >= 0
        and it.parentid >= 0
        and op1.operationsubtype = 1
        and ((it.storageid = :storageid) or (:storageid = 0))
     into  :temp;

     initialrem = :initialrem - :temp;

     for
       select
         SUM(coalesce(it.net, 0)),
         op1.operationsubtype,
         op1.operationtype,
         it.operationid,
         sum(coalesce(it.weight,0)),
         wq.classid
       from  it
         join getworkparties(:partyid,:partydate,null,null,null)
          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 quality wq on (it.operationparam = wq.qualityid)
       where q1.classid = :classid
         and it.ownerid = :ownerid
         and it.state >= 0
         and it.parentid >= 0
         and it.regdate  between :date1 and :date2
         and ((it.net is not null) or (it.weight is not null))
         and ((it.storageid = :storageid) or (:storageid = 0))
       group by it.providerid, it.operationid, op1.operationtype, op1.operationsubtype, it.weight, wq.classid
       into :weight,
         :operationsubtype,
         :operationtype,
         :operationid,
         :weight_pre,
         :wclassid
     do
     begin
       if ((:operationsubtype = 0) and (:operationid <> 17) and (:operationtype <> 4)) then
         income = :income + :weight;

       if (:operationid = 17) then
       begin
         if (:weight > 0) then
           incomereb = :incomereb + :weight;
         else
           outcomereb = :outcomereb + :weight*-1;
       end

       if ((:operationsubtype = 0) and (:operationtype = 4)) then
         inventincome = :inventincome + :weight;

       if (((:operationsubtype = 1) and (:operationtype not in (2,5)) and (:operationid <> 70)) or ((:operationtype = 5)
         and (:wclassid not in (41,141))) ) then
         outcome = :outcome + :weight;

       if ((:operationtype = 5) and (:wclassid in (41,141))) then
         waste3k = :waste3k + :weight;

       if (:operationid = 70) then
         samples = :samples + :weight;

       if (:operationtype = 2) then
         prewaste = :prewaste + :weight_pre * -1;
    end

    afterrem = :initialrem + :income +:incomereb + :inventincome - :outcome -:waste3k - :outcomereb - :samples;

    if ((:initialrem <> 0)  or (:income <> 0) or (:inventincome <> 0) or (:incomereb <> 0)
       or (:outcome <> 0) or (:waste3k <> 0) or (:samples <> 0) or (:outcomereb <> 0) or (:prewaste <> 0)) then
      SUSPEND;

     initialrem = :afterrem;
      afterrem = 0;
      income = 0;
      inventincome = 0;
      incomereb = 0;
      outcome = 0;

      waste3k = 0;
      samples = 0;
      outcomereb = 0;
      prewaste = 0;
    end
  end
END