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

CLIENTSPLANTWASTE

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

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

Описание

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

Определение

CREATE PROCEDURE CLIENTSPLANTWASTE(
    BEGINDATE DATE,
    ENDDATE DATE,
    PBEGINDATE DATE,
    PENDDATE DATE,
    STATE INTEGER)
RETURNS (
    CLIENTNAME VARCHAR(30),
    CLIENTID INTEGER,
    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)
    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
    do begin
      for
        select clients.clientid, clients.clientname, sum(it.net)
        from it
          left join quality on quality.qualityid = it.labqualityid
          left join crop on crop.cropid = quality.cropid
          left join clients on clients.clientid = it.ownerid
        where it.regdate between :begindate and :enddate
          and it.state > -1
          and it.parentid < 1
          and it.storageid = :storageid
          and it.operationid in (1, 5, 12, 29, 31, 54)
          and crop.croptypeid = 1
          and it.ownerid = clients.clientid
        group by clients.clientid, clients.clientname
        into :clientid,
             :clientname,
             :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 (:state = 1) then begin
              ClientWaste = cast (((ClientGrain * FactoryWaste)/FactoryGrain) as integer);
              WasteNet = cast (((SumNet * ClientWaste)/ClientGrain) as integer);
            end
            else begin
              ClientWaste = cast (((ClientGrain * FWaste)/FGrain) as integer);
              WasteNet = cast (((SumNet * ClientWaste)/ClientGrain) 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
end