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

ACC_FORM37B

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

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

Описание

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

Определение

CREATE PROCEDURE ACC_FORM37B(
    DATE1 DATE,
    DATE2 DATE,
    STORAGEID SMALLINT,
    PARTYID INTEGER,
    PARTYDATE DATE,
    INCLASSID INTEGER,
    INCROPID INTEGER,
    INCLIENTID INTEGER)
RETURNS (
    REGDATE DATE,
    INITIALREM INTEGER,
    INCOME INTEGER,
    INVENTINCOME INTEGER,
    INCOMEREB INTEGER,
    OUTCOME INTEGER,
    WASTE3K INTEGER,
    SAMPLES INTEGER,
    OUTCOMEREB INTEGER,
    AFTERREM INTEGER,
    PREWASTE INTEGER,
    CROPNAME VARCHAR(20),
    CLASSNAME VARCHAR(20),
    SORTNAME VARCHAR(30))
AS
declare variable operationsubtype smallint;
declare variable weight integer;
declare variable operationtype integer;
declare variable temp integer;
declare variable operationid integer;
declare variable classid integer;
declare variable weight_pre integer;
declare variable sortid integer;
declare variable wclass smallint;
BEGIN
    initialrem = 0;
    afterrem = 0;
    income = 0;
    inventincome = 0;
    incomereb = 0;
    outcome = 0;
    waste3k = 0;
    samples = 0;
    outcomereb = 0;
    prewaste = 0;

  for
    select
      crop.cropname,
      class.classname,
      q1.classid,
      cropsort.sortname,
      q1.sortid
    from  it
      join getworkparties(:partyid,:partydate,:incropid,null,:storageid)
        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)
      left join cropsort on (q1.sortid = cropsort.sortid)
      left join invoices on (it.invoiceid = invoices.invoiceid)
      left join clients on (invoices.senderid = clients.clientid)
    where it.net is not null
      and it.storageid = :storageid
      and it.regdate <= :date2
      and q1.cropid = :incropid
      and ((q1.classid = :inclassid) or (:inclassid = -1))
      and ((q1.classid is not null) or (:inclassid  <> -1))
      and it.ownerid = :inclientid
      and ((it.state >= 0) or (operation.operationtype = 2))
      and it.parentid >= 0
      and operation.operationsubtype in (0,1)
      and clients.storageid is null
    group by crop.cropname, class.classname, q1.classid, cropsort.sortname, q1.sortid
    order by class.classname
     into
     :cropname,
     :classname,
     :classid,
     :sortname,
     :sortid
  do
  begin
    select
      coalesce(SUM(it.net), 0)
    from  it
      join getworkparties(:partyid,:partydate,:incropid,:classid,:storageid)
        on (it.partyid = getworkparties.partyid)
      left join quality q1 on (it.samplerqualityid = q1.qualityid)
      left join operation  ON (it.operationid = operation.operationid)
      left join invoices on (it.invoiceid = invoices.invoiceid)
      left join clients on (invoices.senderid = clients.clientid)
    where it.net is not null
      and it.storageid = :storageid
      and it.regdate < :date1
      and q1.cropid = :incropid
      and q1.classid = :classid
      and ((q1.sortid = :sortid) or ((:sortid is null) and (q1.sortid is null)) )
      and it.ownerid = :inclientid
      and it.state >= 0
      and it.parentid >= 0
      and operation.operationsubtype = 0
      and clients.storageid is null
    into :initialrem;

    select
      coalesce(SUM(it.net), 0)
    from  it
      join getworkparties(:partyid,:partydate,:incropid,:classid,: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 qualitydata region on (it.BuhQualityid = region.qualityid and
       region.qualitytypeid = 18)
      left join invoices on (it.invoiceid = invoices.invoiceid)
      left join clients on (invoices.senderid = clients.clientid)
    where it.net is not null
      and it.storageid = :storageid
      and it.regdate < :date1
      and q1.cropid = :incropid
      and q1.classid = :classid
      and ((q1.sortid = :sortid) or ((:sortid is null) and (q1.sortid is null)) )
      and it.ownerid = :inclientid
      and it.state >= 0
      and it.parentid >= 0
      and op1.operationsubtype = 1
      and clients.storageid is null
   into
     :temp;

   initialrem = :initialrem - :temp;
   for
     select
       it.regdate
     from  it
       join getworkparties(:partyid,:partydate,:incropid,:classid,: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 qualitydata region on (it.BuhQualityid = region.qualityid and region.qualitytypeid = 18)
       left join invoices on (it.invoiceid = invoices.invoiceid)
       left join clients on (invoices.senderid = clients.clientid)
     where  it.storageid = :storageid
       and it.regdate between :date1 and :date2
       and q1.cropid = :incropid
       and q1.classid = :classid
       and ((q1.sortid = :sortid) or ((:sortid is null) and (q1.sortid is null)) )
       and it.ownerid = :inclientid
       and it.state >= 0
       and it.parentid >= 0
       and ((it.net is not null) or (it.weight is not null))
       and clients.storageid is null
     group by it.regdate
     into :regdate
     do
     begin
       for
         select
           SUM(coalesce(it.net, 0)),
           op1.operationsubtype,
           op1.operationtype,
           it.operationid,
           it.weight,
           wq.classid
         from  it
           join getworkparties(:partyid,:partydate,:incropid,:classid,: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 qualitydata region on (it.BuhQualityid = region.qualityid and region.qualitytypeid = 18)
           left join quality wq on (it.operationparam = wq.qualityid)
           left join invoices on (it.invoiceid = invoices.invoiceid)
           left join clients on (invoices.senderid = clients.clientid)
         where it.regdate = :regdate
           and it.storageid = :storageid
           and q1.cropid = :incropid
           and q1.classid = :classid
           and ((q1.sortid = :sortid) or ((:sortid is null) and (q1.sortid is null)) )
           and it.ownerid = :inclientid
           and it.state >= 0
           and it.parentid >= 0
           and ((it.net is not null) or (it.weight is not null))
           and clients.storageid is null
         group by it.providerid, it.operationid, op1.operationtype, op1.operationsubtype, it.weight, wq.classid
         into :weight,
           :operationsubtype,
           :operationtype,
           :operationid,
           :weight_pre,
           :wclass
         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 (:wclass not in (41,141))) ) then
            outcome = :outcome + :weight;

          if ((:operationtype = 5) and (:wclass 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;

      SUSPEND;

      initialrem = :afterrem;
      afterrem = 0;
      income = 0;
      inventincome = 0;
      incomereb = 0;
      outcome = 0;
      waste3k = 0;
      samples = 0;
      outcomereb = 0;
      prewaste = 0;
    end
  end
END