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

NIK_ACC_DIR_REPORT

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

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

Описание

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

Определение

CREATE PROCEDURE NIK_ACC_DIR_REPORT(
    DATE1 DATE,
    DATE2 DATE,
    PARTYID INTEGER,
    PARTYDATE DATE,
    CLASSID INTEGER,
    STORINGID INTEGER,
    FORMTYPE INTEGER,
    HARVDIV INTEGER,
    STORAGEID INTEGER,
    SORTID 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)
AS
DECLARE VARIABLE clientid INTEGER;
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;
BEGIN
   reb_income = 0;
   reb_outcome = 0;
   initialrem = 0;
   income = 0;
   outcome = 0;
   waste = 0;
   temp = 0;
  FOR
    select 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, null,null,null)
       on (it.partyid = getworkparties.partyid)
     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.labqualityid = 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 <= :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.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 clients.clientfullname, quality.sortid,
      clients.clientid, quality.cropid, crop.cropname,
      class.classname, quality.classid, cropsort.sortname,
      harvestyear,
       crop.croptypeid
    order by crop.cropname, cropsort.sortname, class.classname, case :harvdiv when 1 then coalesce(harvestyear.qualityvalue, 0) else 0 end, clients.clientfullname
       INTO
         :clientfullname,
         :clientid,
         :tempcropid,
         :cropname,
         :classname,
         :sortname,
         :tempclassid,
         :tempsortid,
         :harvestyear,
         :croptype
  do
  begin
    for
      select coalesce(sum(case operation.operationtype when 5 then it.net else it.weight end), 0), operation.operationsubtype  from  it
      join getworkparties(:partyid,:partydate, null,null,null)
       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.labqualityid = harvestyear.qualityid and harvestyear.qualitytypeid = 6)
        left join crop on (quality.cropid = crop.cropid)
      where
        storage.storagetypeid = 1
        and it.ownerid = :clientid
        and crop.croptypeid <> 3
        and it.operationid not in (64, 65)

        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 < :date1
        and it.parentid >=0
        and it.weight is not null
        and (it.weight <> 0 or operation.operationtype = 5 and net <> 0)
        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 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(it.weight),0)
    from  it
     join getworkparties(:partyid,:partydate, null,null,null)
       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.labqualityid = 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, null,null,null)
       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.labqualityid = 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, null,null,null)
       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.labqualityid = 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(it.weight * -1),0) from  it
     join getworkparties(:partyid,:partydate, null,null,null)
       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.labqualityid = harvestyear.qualityid and harvestyear.qualitytypeid = 6)
  where it.operationid in (64, 65)
   and it.regdate <= :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.storageid = :storageid) or ((:storageid = 0) or (:storageid is null) ))
  INTO :waste;

   select coalesce(sum(it.weight * -1),0) from  it
     join getworkparties(:partyid,:partydate, null,null,null)
       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.labqualityid = harvestyear.qualityid and harvestyear.qualitytypeid = 6)
  where it.operationid 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.storageid = :storageid) or ((:storageid = 0) or (:storageid is null) ))
  INTO :dailywaste;

   select coalesce(sum(it.net),0) from  it
     join getworkparties(:partyid,:partydate, null,null,null)
       on (it.partyid = getworkparties.partyid)
   left join operation on (it.operationid = operation.operationid)
   inner join quality sq on (it.samplerqualityid = sq.qualityid)
   left join storage on (it.storageid = storage.storageid)
   left join qualitydata harvestyear on (it.labqualityid = harvestyear.qualityid and harvestyear.qualitytypeid = 6)
   left join quality wq on (it.operationparam = wq.qualityid)
  where operation.operationtype = 5
   and wq.classid in (41,141) --!!!
   and it.regdate <= :date2
   and storage.storagetypeid = 1
   and it.ownerid = :clientid
   and sq.classid = :tempclassid
   and ((sq.sortid = :tempsortid) or ((:tempsortid is null) and (wq.sortid is null)) )
   and ((coalesce(harvestyear.qualityvalue, 0) = :harvestyear) or (:harvdiv <> 1))
   and ((it.storingid = :storingid) or (:storingid is null) or (:storingid = 0) )
   and it.state = -10
   and ((it.storageid = :storageid) or ((:storageid = 0) or (:storageid is null) ))
  INTO :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