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

CLIENTPLANTWASTE

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

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

Описание

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

Определение

CREATE PROCEDURE CLIENTPLANTWASTE(
    CLIENTID INTEGER,
    ADDINGGRAIN INTEGER,
    CROPCLASS INTEGER,
    BEGINDATE DATE,
    ENDDATE DATE,
    PBEGINDATE DATE,
    PENDDATE DATE,
    STATE INTEGER)
RETURNS (
    CROPNAME VARCHAR(20),
    CLASSNAME VARCHAR(20),
    STORAGENAME VARCHAR(100),
    CLASSID INTEGER,
    NET INTEGER,
    CULTURENET INTEGER,
    UNLOADNET INTEGER,
    SUMNET INTEGER,
    WASTENET INTEGER,
    ITWASTENET INTEGER)
AS
DECLARE VARIABLE CLIENTWASTE INTEGER;
DECLARE VARIABLE FACTORYWASTE INTEGER;
DECLARE VARIABLE STORAGEID INTEGER;
DECLARE VARIABLE CLIENTGRAIN INTEGER;
DECLARE VARIABLE FACTORYGRAIN INTEGER;
DECLARE VARIABLE FGRAIN INTEGER;
DECLARE VARIABLE FWASTE INTEGER;
begin
  for
    select storage.storageid, storage.storagename,
           sum(case when (it.operationid in (1, 5, 12, 29, 31, 54) and croptypeid = 1
                          and regdate between :begindate and :enddate) then it.net else null end),
           sum(case when (it.operationid = 5 and classid in (40, 41)
                          and regdate between :begindate and :enddate) then it.net else null end),
           sum(case when (it.operationid in (1, 5, 12, 29, 31, 54) and croptypeid = 1
                          and regdate between :pbegindate and :penddate) then it.net else null end),
           sum(case when (it.operationid = 5 and classid in (40, 41)
                          and regdate between :pbegindate and :penddate) then it.net else null end),
           sum(case when (it.operationid in (1, 5, 12, 29, 31, 54) and croptypeid = 1 and it.ownerid = :clientid
                          and regdate between :begindate and :enddate) then it.net else null end)
    from it
      left join quality on quality.qualityid = it.labqualityid
      left join crop on crop.cropid = quality.cropid
      left join storage on storage.storageid = it.storageid
    where it.state > -1
      and it.parentid < 1
      and it.regdate between coalesce(:pbegindate, :begindate) and :enddate
      and it.operationid in (1, 5, 12, 29, 31, 54)
    group by storage.storageid, storage.storagename
    into :storageid,
         :storagename,
         :FactoryGrain,
         :FactoryWaste,
         :FGrain,
         :FWaste,
         :ClientGrain
    do begin
          for
            select crop.cropname, class.classname, class.classid,
                   sum(case when (it.operationid = 1 and croptypeid = 1)
                            then it.net else null end),
                   sum(case when (it.operationid in (12, 29, 31, 54) and
                                  croptypeid = 1) then it.net else null end),
                   sum(case when (it.operationid = 5 and croptypeid = 1)
                            then it.net else null end),
                   sum(case when (it.operationid in (1, 5, 12, 29, 31, 54) and
                                  croptypeid = 1) then it.net else null end),
                   sum(case when (it.operationid = 22) then it.net else null end)
            from it
              left join quality on quality.qualityid = it.labqualityid
              left join class on class.classid = quality.classid
              left join crop on crop.cropid = quality.cropid
            where it.regdate between :begindate and :enddate
              and it.state > -1
              and it.parentid < 1
              and it.ownerid = :clientid
              and it.storageid = :storageid
              and it.operationid in (1, 5, 12, 22, 29, 31, 54)
            group by crop.cropname, class.classname, class.classid
            into :CropName,
                 :ClassName,
                 :ClassID,
                 :Net,
                 :CultureNet,
                 :UnloadNet,
                 :SumNet,
                 :ITWasteNet
          do begin
            if (:cropclass = :classid) then begin
              sumnet = coalesce(sumnet, 0) + addinggrain;
              unloadnet = coalesce(unloadnet, 0) + addinggrain;
            end
            if (:state = 1) then begin
              ClientWaste = cast ((((ClientGrain + AddingGrain) * FactoryWaste)/(FactoryGrain + AddingGrain)) as integer);
              WasteNet = cast (((SumNet * ClientWaste)/(ClientGrain + AddingGrain)) as integer);
            end
            else begin
              ClientWaste = cast ((((ClientGrain + AddingGrain) * FWaste)/(FGrain + AddingGrain)) as integer);
              WasteNet = cast (((SumNet * ClientWaste)/(ClientGrain  + AddingGrain)) as integer);
            end
              if (:WasteNet = 0) then WasteNet = null;
              if (:ITWasteNet = 0) then ITWasteNet = null;
              if ((:net is not null) or (:culturenet is not null) or (:unloadnet is not null) or
                 (:sumnet is not null) or (:wastenet is not null) or (:itwastenet is not null))
                then suspend;
          end

    end
end