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

UNDERWORK_CALCULATE_WASTE

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

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

Описание

Корректировка отходов 3-й категории и не только по вызенным

Определение

CREATE PROCEDURE UNDERWORK_CALCULATE_WASTE(
    UWORDERID INTEGER,
    UWTYPEID INTEGER,
    METODSOLUTION INTEGER,
    FINALCALCULATION INTEGER,
    ABEFOREDRYINGWEIGHT INTEGER,
    AAFTERDRYINGWEIGHT INTEGER,
    DRYING NUMERIC(9,2))
RETURNS (
    ALLDIRTWEIGHT INTEGER,
    ALLSCREENWEIGHT INTEGER,
    BEFOREDRYINGWEIGHT INTEGER,
    AFTERDRYINGWEIGHT INTEGER,
    INVENTORYID INTEGER,
    INVOICEID INTEGER)
AS
declare variable SETTLEMENTDIRTWEIGHT integer;
declare variable SETTLEMENTSCREENWEIGHT integer;
declare variable DIRTCOEFFICIENT numeric(10,8) = 0;
declare variable SCREENCOEFFICIENT numeric(10,8) = 0;
declare variable OWNERID integer;
declare variable AINVENTORYID integer;
declare variable ADIRTWEIGHT integer;
declare variable ASCREENWEIGHT integer;
declare variable DIRTWEIGHT integer;
declare variable SCREENWEIGHT integer;
declare variable ASUMDIRTWEIGHT integer;
declare variable ASUMSCREENWEIGHT integer;
declare variable SUMDIRTWEIGHT integer = 0;
declare variable SUMSCREENWEIGHT integer = 0;
declare variable ANET integer;
declare variable AWEIGHT integer;
declare variable GROSS integer;
declare variable TARE integer;
declare variable NET integer;
declare variable WEIGHT integer;
declare variable SETTLEMENTSHRINKAGEWEIGHT integer;
declare variable SHRINKAGEWEIGHT integer;
declare variable SHRINKAGECOEFFICIENT numeric(10,8);
declare variable AHUMWEIGHT integer;
declare variable HUMWEIGHT integer;
declare variable ASUMHUMWEIGHT integer;
declare variable SUMHUMWEIGHT integer = 0;
begin
  ALLDIRTWEIGHT = 0;
  ALLSCREENWEIGHT = 0;
  BEFOREDRYINGWEIGHT = ABEFOREDRYINGWEIGHT;
  AFTERDRYINGWEIGHT = AAFTERDRYINGWEIGHT;

  select
      sum(case
            when ((IT.OPERATIONID = 5) and (QUALITY.CLASSID in (41))) then
              IT.NET
            end),
      sum(case
            when (IT.OPERATIONID = 4) then
              IT.NET
            end),
      coalesce(sum(case
                 when ((IT.OPERATIONID = 5) and (QUALITY.CLASSID not in (41))) then
                   IT.NET
                 end),0),
      coalesce(sum(case
                 when (IT.OPERATIONID = 4) then
/*
              case
                when (:METODSOLUTION in (46,146)) then
                  IT.NET
                else IT.WEIGHT
              end
*/
                   IT.WEIGHT
                 end),0)
    from IT
      left join QUALITY on (QUALITY.QUALITYID = IT.LABQUALITYID)
      left join CROP on (CROP.CROPID = QUALITY.CROPID)
    where ((IT.UWORDERID = :UWORDERID) and ((IT.NET <> 0) or (IT.WEIGHT <> 0)))
      and (((IT.STATE > -1) and ((IT.OPERATIONID = 5) and (CROP.CROPTYPEID = 3)))
           or ((IT.STATE = -999) and (IT.OPERATIONID = 4)))
  group by IT.UWORDERID
  into :ALLDIRTWEIGHT, :SETTLEMENTDIRTWEIGHT, :ALLSCREENWEIGHT, :SETTLEMENTSCREENWEIGHT;

  if (:FINALCALCULATION = 1) then begin
    if (SETTLEMENTDIRTWEIGHT > 0) then
      DIRTCOEFFICIENT = cast((cast(ALLDIRTWEIGHT as numeric (15,8)) / SETTLEMENTDIRTWEIGHT) as numeric (9,8));

    if ((ALLSCREENWEIGHT <> 0) and (SETTLEMENTSCREENWEIGHT = 0)) then begin
      SETTLEMENTSCREENWEIGHT = SETTLEMENTDIRTWEIGHT;
    end

    if (SETTLEMENTSCREENWEIGHT > 0) then
      SCREENCOEFFICIENT = cast((cast(ALLSCREENWEIGHT as numeric (15,8)) / SETTLEMENTSCREENWEIGHT) as numeric (10,8));

    for
      select
          IT.OWNERID, max(IT.INVENTORYID), sum(IT.NET),
          sum(case
                when (:METODSOLUTION in (46,146)) then
                  IT.NET
                else IT.WEIGHT
              end)
        from IT
        where (IT.UWORDERID = :UWORDERID) and ((IT.NET <> 0) or (IT.WEIGHT <> 0))
          and (IT.STATE = -999) and (IT.OPERATIONID = 4)
      group by IT.OWNERID
      into :OWNERID, :AINVENTORYID, :ADIRTWEIGHT, :ASCREENWEIGHT

    do begin
      DIRTWEIGHT = round(ADIRTWEIGHT * DIRTCOEFFICIENT);
      ASUMDIRTWEIGHT = 0;
      SCREENWEIGHT = round(ASCREENWEIGHT * SCREENCOEFFICIENT);
      ASUMSCREENWEIGHT = 0;

      for
        select
            IT.INVENTORYID, IT.INVOICEID, IT.GROSS, coalesce(IT.NET,0),
            coalesce(case
                       when (:METODSOLUTION in (46,146)) then
                         IT.NET
                       else IT.WEIGHT
                     end,0) WEIGHT
          from IT
          where (IT.UWORDERID = :UWORDERID) and ((IT.NET <> 0) or (IT.WEIGHT > 0))
            and (IT.STATE = -999) and (IT.OPERATIONID = 4) and (IT.OWNERID = :OWNERID)
        into :INVENTORYID, :INVOICEID, :GROSS, :ANET, :AWEIGHT

      do begin
        SETTLEMENTDIRTWEIGHT = SETTLEMENTDIRTWEIGHT - ANET;
        SETTLEMENTSCREENWEIGHT = SETTLEMENTSCREENWEIGHT - AWEIGHT;

        if (INVENTORYID = AINVENTORYID) then begin
          if (SETTLEMENTDIRTWEIGHT = 0) then
            NET = ALLDIRTWEIGHT - SUMDIRTWEIGHT;
          else NET = DIRTWEIGHT - ASUMDIRTWEIGHT;

          if (SETTLEMENTSCREENWEIGHT = 0) then
            WEIGHT = ALLSCREENWEIGHT - SUMSCREENWEIGHT;
          else WEIGHT = SCREENWEIGHT - ASUMSCREENWEIGHT;
        end
        else begin
          NET = round(ANET * DIRTCOEFFICIENT);
          WEIGHT = round(AWEIGHT * SCREENCOEFFICIENT);
        end

        ASUMDIRTWEIGHT = ASUMDIRTWEIGHT + NET;
        SUMDIRTWEIGHT = SUMDIRTWEIGHT + NET;
        ASUMSCREENWEIGHT = ASUMSCREENWEIGHT + WEIGHT;
        SUMSCREENWEIGHT = SUMSCREENWEIGHT + WEIGHT;
        TARE = GROSS - NET - WEIGHT;

        update IT
        set TARE = :TARE,
            NET = :NET,
            WEIGHT = :WEIGHT
        where (INVENTORYID = :INVENTORYID);

        INVENTORYID = null;

        if ((:UWTYPEID <> 34) and (:METODSOLUTION in (46,146))) then begin
          select IT.INVENTORYID, IT.GROSS
            from IT
            where (IT.UWORDERID = :UWORDERID) and (IT.INVOICEID = :INVOICEID)
              and (IT.NET <> 0) and (IT.STATE = -999) and (IT.OPERATIONID = 9)
          into :INVENTORYID, :GROSS;

          if (INVENTORYID is not null) then begin
            update IT
            set GROSS = :TARE
            where (INVENTORYID = :INVENTORYID);
          end
        end
      end
    end

    if ((:UWTYPEID <> 34) and (:METODSOLUTION in (46,146))) then begin
      select sum(IT.GROSS), sum(IT.NET)
        from IT
        where (IT.UWORDERID = :UWORDERID) and (IT.NET > 0)
          and (IT.STATE = -999) and (IT.OPERATIONID = 9)
      into :BEFOREDRYINGWEIGHT, :SETTLEMENTSHRINKAGEWEIGHT;

      if (BEFOREDRYINGWEIGHT <> 0) then begin
        SHRINKAGEWEIGHT = round(BEFOREDRYINGWEIGHT * DRYING / 100);
        AFTERDRYINGWEIGHT = BEFOREDRYINGWEIGHT - SHRINKAGEWEIGHT;
        SHRINKAGECOEFFICIENT = cast((cast(SHRINKAGEWEIGHT as numeric (15,8)) / SETTLEMENTSHRINKAGEWEIGHT) as numeric (9,8));

        for
          select
              IT.OWNERID, max(IT.INVENTORYID), sum(IT.NET)
            from IT
            where (IT.UWORDERID = :UWORDERID) and (IT.NET <> 0)
              and (IT.STATE = -999) and (IT.OPERATIONID = 9)
          group by IT.OWNERID
          into :OWNERID, :AINVENTORYID, :AHUMWEIGHT

        do begin
          HUMWEIGHT = round(AHUMWEIGHT * SHRINKAGECOEFFICIENT);
          ASUMHUMWEIGHT = 0;

          for
            select
                IT.INVENTORYID, IT.NET
              from IT
              where (IT.UWORDERID = :UWORDERID) and (IT.NET <> 0)
                and (IT.STATE = -999) and (IT.OPERATIONID = 9)
                and (IT.OWNERID = :OWNERID)
            into :INVENTORYID, :ANET

          do begin
            SETTLEMENTSHRINKAGEWEIGHT = SETTLEMENTSHRINKAGEWEIGHT - ANET;

            if (INVENTORYID = AINVENTORYID) then begin
              if (SETTLEMENTSHRINKAGEWEIGHT = 0) then
                NET = SHRINKAGEWEIGHT - SUMHUMWEIGHT;
              else NET = HUMWEIGHT - ASUMHUMWEIGHT;
            end
            else NET = round(ANET * SHRINKAGECOEFFICIENT);

            ASUMHUMWEIGHT = ASUMHUMWEIGHT + NET;
            SUMHUMWEIGHT = SUMHUMWEIGHT + NET;

            update IT
            set NET = :NET
            where (INVENTORYID = :INVENTORYID);
          end
        end
      end
      else AFTERDRYINGWEIGHT = BEFOREDRYINGWEIGHT;
    end
  end

  suspend;
end