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

ACC_MOVE_REPORT

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

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

Описание

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

Определение

CREATE PROCEDURE ACC_MOVE_REPORT(
    BEGINDATE DATE,
    ENDDATE DATE,
    PARTYID INTEGER,
    PARTYDATE DATE)
RETURNS (
    INITIAL_NET INTEGER,
    INCOMENETAVTO INTEGER,
    INCOMENETRW INTEGER,
    INCOMENETSH INTEGER,
    INCOMENET INTEGER,
    OUTCOMENETAVTO INTEGER,
    OUTCOMENETRW INTEGER,
    OUTCOMENETSH INTEGER,
    OUTCOMENET INTEGER,
    WRITTENWASTE INTEGER,
    AVALIABLE_NET INTEGER,
    CROPFULLNAME VARCHAR(100),
    ACROPID INTEGER,
    CROPNAME VARCHAR(20))
AS
declare variable ACLASSID integer;
begin
  ACLASSID = null;
  ACROPID = null;
  for
    select CROPNAME, CLASS.CROPID, CLASS.CLASSID, QUALITY_CROPFULLNAME.CROPFULLNAME
      from CROP
        left join CLASS on (CLASS.CROPID = CROP.CROPID)
        left join QUALITY_CROPFULLNAME(CROP.CROPID, CLASS.CLASSID, null, 0) on (0 = 0)
      where (CROP.CROPTYPEID in (1, 5))
    order by QUALITY_CROPFULLNAME.CROPFULLNAME
    into :CROPNAME, :ACROPID, :ACLASSID, :CROPFULLNAME

  do begin
    INITIAL_NET = 0;
    INCOMENETAVTO = 0;
    INCOMENETRW = 0;
    INCOMENETSH = 0;
    OUTCOMENETAVTO = 0;
    OUTCOMENETRW = 0;
    OUTCOMENETSH = 0;
    WRITTENWASTE = 0;
    AVALIABLE_NET = 0;
    for
      select
        coalesce(sum(case
                       when ((IT.OPERATIONID = 1) and (ITTRANSPORT.TRANSPORTTYPEID in (0, 1)) and (IT.REGDATE >= :BEGINDATE)) then
                         IT.NET
                     end), 0) INCOMENETAVTO,
        coalesce(sum(case
                       when ((IT.OPERATIONID = 1) and (ITTRANSPORT.TRANSPORTTYPEID = 2) and (IT.REGDATE >= :BEGINDATE)) then
                          IT.NET
                     end), 0) INCOMENETRW,
        coalesce(sum(case
                       when ((IT.OPERATIONID = 5) and (ITTRANSPORT.TRANSPORTTYPEID in (0, 1)) and (IT.REGDATE >= :BEGINDATE)) then
                         IT.NET
                     end), 0) OUTCOMENETAVTO,
        coalesce(sum(case
                       when ((IT.OPERATIONID = 5) and (ITTRANSPORT.TRANSPORTTYPEID = 2) and (IT.REGDATE >= :BEGINDATE)) then
                          IT.NET
                     end), 0) OUTCOMENETRW,
        coalesce(sum(case
                       when ((IT.OPERATIONID = 5) and (ITTRANSPORT.TRANSPORTTYPEID = 3) and (IT.REGDATE >= :BEGINDATE)) then
                          IT.NET
                     end), 0) OUTCOMENETSH,
        coalesce(sum(case
                       when ((IT.OPERATIONID in (9, 22)) and (IT.REGDATE >= :BEGINDATE)) then
                          IT.NET
                     end), 0) WRITTENWASTE,
        coalesce(sum(case
                       when ((IT.OPERATIONID in (1, 18)) and (IT.REGDATE < :BEGINDATE)) then
                         IT.NET
                       when ((IT.OPERATIONID in (5, 9, 22)) and (IT.REGDATE < :BEGINDATE)) then
                         -IT.NET
                     end), 0) INITIAL_NET
        from IT
          join GETWORKPARTIES(:PARTYID, :PARTYDATE, :ACROPID, :ACLASSID, null) on (GETWORKPARTIES.PARTYID = IT.PARTYID)
          left join QUALITY on (QUALITY.QUALITYID = IT.SAMPLERQUALITYID)
          left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
        where (IT.REGDATE <= :ENDDATE)
          and ((QUALITY.CROPID = :ACROPID) and (QUALITY.CLASSID = :ACLASSID))
          and (IT.STATE >= 0)
          and (IT.PARENTID >= 0)
          and ((not IT.NET is null) and (IT.NET <> 0))
          and (IT.OPERATIONID in (1, 5, 9, 18, 22))
      into :INCOMENETAVTO, :INCOMENETRW,
           :OUTCOMENETAVTO, :OUTCOMENETRW, :OUTCOMENETSH, :WRITTENWASTE,
           :INITIAL_NET

      do begin

        AVALIABLE_NET = :INITIAL_NET + :INCOMENETAVTO + :INCOMENETRW + :INCOMENETSH - :OUTCOMENETAVTO - :OUTCOMENETRW - :OUTCOMENETSH - :WRITTENWASTE;
        INCOMENET = :INCOMENETAVTO + :INCOMENETRW + :INCOMENETSH;
        OUTCOMENET = :OUTCOMENETAVTO + :OUTCOMENETRW + :OUTCOMENETSH;

        if ((:INITIAL_NET + :INCOMENETAVTO + :INCOMENETRW + :INCOMENETSH + :OUTCOMENETAVTO + :OUTCOMENETRW + :OUTCOMENETSH) <> 0) then
         suspend;

      end
   end
end