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

ACC_DIR_REPORT

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

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

Описание

Отчет для директора
Форма 37 (отчет за день)

Определение

CREATE PROCEDURE ACC_DIR_REPORT(
    DATE1 DATE,
    DATE2 DATE,
    PARTYID INTEGER,
    PARTYDATE DATE,
    CLASSID INTEGER,
    STORINGID INTEGER,
    FORMTYPE INTEGER,
    HARVDIV INTEGER,
    STORAGEID INTEGER,
    SORTID INTEGER,
    PLUGINPARAM INTEGER,
    AOWNERID INTEGER)
RETURNS (
    CLIENTFULLNAME VARCHAR(100),
    INITIALREM INTEGER,
    CROPNAME VARCHAR(100),
    CLASSNAME VARCHAR(50),
    SORTNAME VARCHAR(30),
    HARVESTYEAR SMALLINT,
    INCOME INTEGER,
    OUTCOME INTEGER,
    REB_INCOME INTEGER,
    REB_OUTCOME INTEGER,
    AVAILABILE INTEGER,
    WASTE INTEGER,
    WASTE3K INTEGER,
    CLIENTID INTEGER,
    MINDATE DATE)
AS
declare variable tempcropid integer;
declare variable tempclassid integer;
declare variable tempsortid integer;
declare variable temp integer;
declare variable operationsubtype integer;
declare variable dailywaste integer;
declare variable croptype smallint;
declare variable cropid integer;
BEGIN
   reb_income = 0;
   reb_outcome = 0;
   initialrem = 0;
   income = 0;
   outcome = 0;
   waste = 0;
   temp = 0;
   cropid = null;
   select cropid from class where classid = :classid
   into :cropid;

   select min(it.regdate)
   from it
     join getworkparties(:partyid,:partydate,:cropid,:classid,:storageid)
       on (it.partyid = getworkparties.partyid)
   where it.state <> -1
   INTO :mindate;

  FOR
    select --distinct
      clients.clientfullname,
      clients.clientid,
      quality.cropid,
      crop.cropname,
      class.classname,
      cropsort.sortname,
      quality.classid,
      quality.sortid,
      case :harvdiv when 1 then coalesce(harvestyear.qualityvalue, 0) else 0 end harvestyear,
      crop.croptypeid
    from  it
      join getworkparties(:partyid,:partydate,:cropid,:classid,:storageid)
        on (it.partyid = getworkparties.partyid)
     inner join clients on (it.ownerid = clients.clientid)
     left join operation on (it.operationid = operation.operationid)
     inner join quality on (it.samplerqualityid = quality.qualityid)
     left join quality_cropfullname(quality.cropid, quality.classid, null, 1) qn on 0 = 0
     left join crop on (quality.cropid = crop.cropid)
     left join class on (quality.classid = class.classid)
     left join cropsort on (quality.sortid = cropsort.sortid)
     left join qualitydata harvestyear on (it.BuhQualityid = harvestyear.qualityid
       and harvestyear.qualitytypeid = 6)
     left join storage on (it.storageid = storage.storageid)
   where operation.operationsubtype in (0,1)
    and storage.storagetypeid = 1
    and it.regdate between :mindate and :date2
    and (quality.classid = :classid or (:classid is null) or (:classid  = -1))
    and ((quality.sortid = :sortid) or (:sortid = 0) or (:sortid is null) or (quality.sortid is null))
    and it.parentid >=0
    and (it.ownerid = :aownerid or (coalesce(:aownerid,-1) = -1))
    and it.weight is not null
    and (((crop.croptypeid <> 3) and ((:classid = -1) or (:classid is null))) or (:classid <> -1))
    and (it.weight <> 0 or operation.operationtype = 5)
    and ((it.storingid = :storingid) or (:storingid is null) or (:storingid = 0) )
    and it.state >=0
    and ((it.storageid = :storageid) or ((:storageid = 0) or (:storageid is null) ))
    group by crop.cropname, cropsort.sortname, class.classname,
      harvestyear,
      clients.clientfullname, quality.sortid,
      clients.clientid, quality.cropid,
      quality.classid,
       crop.croptypeid
    INTO
         :clientfullname,
         :clientid,
         :tempcropid,
         :cropname,
         :classname,
         :sortname,
         :tempclassid,
         :tempsortid,
         :harvestyear,
         :croptype
  do
  begin
    for
      select
        coalesce(sum(it.net), 0),
        operation.operationsubtype
      from  it
        join getworkparties(:partyid,:partydate,:tempcropid,:tempclassid,:storageid)
        on (it.partyid = getworkparties.partyid)
        left join operation on (it.operationid = operation.operationid)
        inner join quality on (it.samplerqualityid = quality.qualityid)
        left join storage on (it.storageid = storage.storageid)
        left join qualitydata harvestyear on (it.BuhQualityid = harvestyear.qualityid and harvestyear.qualitytypeid = 6)
        left join crop on (quality.cropid = crop.cropid)
      where it.regdate < :date1
         and it.regdate between :mindate and :date1 --!
        and it.ownerid = :clientid
        and it.parentid >=0
        and (  (it.weight is not null or (it.operationid in (9,22))
             or (it.weight <> 0 or operation.operationtype = 5 and net <> 0)))
        and it.operationid not in (64, 65)
        and ((it.storingid = :storingid) or (:storingid is null) or (:storingid = 0) )
        and it.state >=0
        and ((it.storageid = :storageid) or ((:storageid = 0) or (:storageid is null) ))
        and storage.storagetypeid = 1
        and crop.croptypeid <> 3
        and quality.classid = :tempclassid
        and ((quality.sortid = :tempsortid) or ((:tempsortid is null) and (quality.sortid is null)) )
        and ((coalesce(harvestyear.qualityvalue, 0) = :harvestyear) or (:harvdiv <> 1))
      group by quality.cropid, it.ownerid, operation.operationsubtype
      into
        :temp,
        :operationsubtype
      do
      begin
       if (:operationsubtype = 0) then
        initialrem = :initialrem + :temp;
       else if (:operationsubtype = 1) then
        initialrem = :initialrem - :temp;
      end


    select coalesce(sum(case when not coalesce(:pluginparam,0) in (4,5,6)
              then it.weight  else it.net
                    end),0)
    from  it
       join getworkparties(:partyid,:partydate,:tempcropid,:tempclassid,:storageid)
        on (it.partyid = getworkparties.partyid)
      left join operation on (it.operationid = operation.operationid)
      inner join quality on (it.samplerqualityid = quality.qualityid)
      left join storage on (it.storageid = storage.storageid)
      left join qualitydata harvestyear on (it.BuhQualityid = harvestyear.qualityid and harvestyear.qualitytypeid = 6)
      left join crop on (:tempcropid = crop.cropid)
    where ((operation.operationsubtype = 0) or ((crop.croptypeid = 3) and operation.operationsubtype in (0,1)))
      and it.operationid <> 17
      and storage.storagetypeid = 1
      and it.ownerid = :clientid
      and quality.classid = :tempclassid
      and ((quality.sortid = :tempsortid) or ((:tempsortid is null) and (quality.sortid is null)) )
      and ((coalesce(harvestyear.qualityvalue, 0) = :harvestyear) or (:harvdiv <> 1))
      and it.regdate between :date1 and :date2
      and it.parentid >=0
      and it.weight is not null
      and ((it.storingid = :storingid) or (:storingid is null) or (:storingid = 0) )
      and it.weight <> 0
      and it.state >=0
      and ((it.storageid = :storageid) or ((:storageid = 0) or (:storageid is null) ))
   group by quality.cropid, it.ownerid
      INTO :income;

    for
    select coalesce((it.weight),0)
    from  it
      join getworkparties(:partyid,:partydate, :tempcropid,:tempclassid,:storageid)
        on (it.partyid = getworkparties.partyid)
    left join operation on (it.operationid = operation.operationid)
    inner join quality on (it.samplerqualityid = quality.qualityid)
    left join storage on (it.storageid = storage.storageid)
    left join qualitydata harvestyear on (it.BuhQualityid = harvestyear.qualityid
      and harvestyear.qualitytypeid = 6)
   where it.operationid = 17
    and storage.storagetypeid = 1
    and it.ownerid = :clientid
    and quality.classid = :tempclassid
    and ((quality.sortid = :tempsortid) or ((:tempsortid is null) and (quality.sortid is null)) )
    and ((coalesce(harvestyear.qualityvalue, 0) = :harvestyear) or (:harvdiv <> 1))
    and it.regdate between :date1 and :date2
    and it.parentid >=0
    and it.weight is not null
    and it.weight <> 0
    and it.state >=0
    and ((it.storingid = :storingid) or (:storingid is null) or (:storingid = 0) )
    and ((it.storageid = :storageid) or ((:storageid = 0) or (:storageid is null) ))
   INTO :temp
    do
    begin
     if (:temp >= 0) then
       reb_income = :reb_income + :temp;
     else
       reb_outcome = :reb_outcome + (:temp*-1);
    end

   select coalesce(sum(it.net),0)
   from  it
      join getworkparties(:partyid,:partydate,:tempcropid,:tempclassid,:storageid)
        on (it.partyid = getworkparties.partyid)
     left join operation on (it.operationid = operation.operationid)
     inner join quality on (it.samplerqualityid = quality.qualityid)
     left join storage on (it.storageid = storage.storageid)
     left join qualitydata harvestyear on (it.BuhQualityid = harvestyear.qualityid
       and harvestyear.qualitytypeid = 6)
    where
     ((operation.operationsubtype = 1) and (it.operationid not in (64, 65)))
     and it.regdate between :date1 and :date2
     and storage.storagetypeid = 1
     and it.ownerid = :clientid
     and quality.classid = :tempclassid
     and ((quality.sortid = :tempsortid) or ((:tempsortid is null) and (quality.sortid is null)) )
     and ((coalesce(harvestyear.qualityvalue, 0) = :harvestyear) or (:harvdiv <> 1))
     and it.parentid >=0
     and ((it.storingid = :storingid) or (:storingid is null) or (:storingid = 0) )
     and it.state >=0
     and it.net is not null  and it.net <> 0
     and ((it.storageid = :storageid) or ((:storageid = 0) or (:storageid is null) ))
   INTO :outcome;

   select
     coalesce(sum(
       case when (it.operationid in (64, 65) and it.state >=0)
       then it.weight * -1 end),0),
     coalesce(sum(case when it.regdate between :date1 and :date2
      and (it.operationid in (64, 65) and it.state >=0)
     then it.weight * -1 end),0),
     coalesce(sum(case
       when (operation.operationtype = 5 and wq.classid in (41,141) and it.state = -10)
       then it.net end),0)
   from  it
    join getworkparties(:partyid,:partydate,:tempcropid,:tempclassid,:storageid)
      on (it.partyid = getworkparties.partyid)
     left join operation on (it.operationid = operation.operationid)
     inner join quality q1 on (it.samplerqualityid = q1.qualityid)
     left join storage on (it.storageid = storage.storageid)
     left join qualitydata harvestyear on (it.BuhQualityid = harvestyear.qualityid
       and harvestyear.qualitytypeid = 6)
     left join quality wq on (it.operationparam = wq.qualityid)
  where it.regdate <= :date2
   and it.regdate between :mindate and :date2 --!

   and  ( (it.operationid in (64, 65) and it.state >=0)
        or (operation.operationtype = 5 and wq.classid in (41,141) and it.state = -10)
        )
   and storage.storagetypeid = 1
   and it.ownerid = :clientid
   and q1.classid = :tempclassid
   and ((q1.sortid = :tempsortid) or ((:tempsortid is null) and (q1.sortid is null)) )
   and ((coalesce(harvestyear.qualityvalue, 0) = :harvestyear) or (:harvdiv <> 1))
   and it.parentid >=0
   and ((it.storingid = :storingid) or (coalesce(:storingid,0) = 0) )
   and ((it.storageid = :storageid) or (coalesce(:storageid,0) = 0) )
  INTO :waste,
       :dailywaste,
       :waste3k;

  if (:formtype = 1) then
    initialrem = :initialrem  - :waste  + :dailywaste;

  if (:croptype <> 3) then
  begin
    availabile = :initialrem + :income - :outcome + :reb_income - :reb_outcome;

    if (:formtype = 1) then
      availabile = :availabile  - :dailywaste;
  end

   if ((:initialrem <> 0) or (:income <> 0) or (:reb_income <> 0) or (:reb_outcome <> 0)
      or (:outcome <> 0) or (:waste <> 0) and (:formtype = 0)) then

    SUSPEND;
   reb_income = 0;
   reb_outcome = 0;
   initialrem = 0;
   income = 0;
   outcome = 0;
   waste = 0;
   temp = 0;
  END
END