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

UNDERWORK_CALCUWACTDRY

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

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

Описание

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

Определение

CREATE PROCEDURE UNDERWORK_CALCUWACTDRY(
    UWORDERDATE DATE,
    UWACTDATE DATE,
    UWORDERID INTEGER,
    QUALITYBEFOREID INTEGER,
    QUALITYAFTERID INTEGER,
    CROPID INTEGER,
    CLASSID INTEGER,
    UWTYPEID INTEGER,
    SORTID INTEGER,
    CLIENTID INTEGER,
    FINALCALCULATION INTEGER,
    STORINGID INTEGER,
    CONTRACTID INTEGER)
RETURNS (
    BEFOREWEIGHT INTEGER,
    STORAGEID INTEGER)
AS
declare variable METODSOLUTION integer;
declare variable RWEIGHT double precision;
declare variable BEFOREDRYINGQUALITYID integer;
declare variable ACROPID integer;
declare variable ACLASSID integer;
declare variable QUALITYNUMBERBEFORE integer;
declare variable AQUALITYNUMBERBEFORE integer;
declare variable ANALYSISDATEBEFORE date;
declare variable HUMIDITYBEFORE numeric(9,2);
declare variable DIRTBEFORE numeric(9,2);
declare variable SCREENINGBEFORE numeric(9,2);
declare variable GRAINUNITBEFORE integer;
declare variable SMALLGRAINBEFORE numeric(9,2);
declare variable AFTERDRYINGQUALITYID integer;
declare variable QUALITYNUMBERAFTER integer;
declare variable AQUALITYNUMBERAFTER integer;
declare variable ANALYSISDATEAFTER date;
declare variable HUMIDITYAFTER numeric(9,2);
declare variable DIRTAFTER numeric(9,2);
declare variable SCREENINGAFTER numeric(9,2);
declare variable GRAINUNITAFTER integer;
declare variable SMALLGRAINAFTER numeric(9,2);
declare variable HARVESTYEAR integer;
declare variable RBEGINDATE date;
declare variable RENDDATE date;
declare variable DRYING numeric(9,2);
declare variable OPERATIONID integer;
declare variable REGDATE date;
declare variable PARTYID integer;
declare variable INVOICEID integer;
declare variable ACONTRACTID integer;
declare variable OWNERID integer;
declare variable AOWNERID integer;
declare variable ASTORINGID integer;
declare variable WEIGHTBEFOREDRYING integer;
declare variable DIRT_BEFORE numeric(15,2) = 0;
declare variable SCREENING_BEFORE numeric(15,2) = 0;
declare variable HUMIDITY_BEFORE numeric(15,2) = 0;
declare variable GRAINUNIT_BEFORE numeric(15,2) = 0;
declare variable SMALLGRAIN_BEFORE numeric(15,2) = 0;
declare variable DIRT_AFTER numeric(15,2) = 0;
declare variable SCREENING_AFTER numeric(15,2) = 0;
declare variable HUMIDITY_AFTER numeric(15,2) = 0;
declare variable GRAINUNIT_AFTER numeric(15,2) = 0;
declare variable SMALLGRAIN_AFTER numeric(15,2) = 0;
declare variable HUMWEIGHT integer;
declare variable INVENTORYID integer;
declare variable ALLWEIGHTBEFOREDRYING double precision;
declare variable SHRINKAGEWEIGHT integer;
declare variable SETTLEMENTSHRINKAGEWEIGHT integer;
declare variable THEORETICALSHRINKAGEWEIGHT integer;
declare variable COEFFICIENT numeric(9,8);
declare variable COEFFICIENT_ALL numeric(9,8);
declare variable SUMHUMWEIGHT_ALL integer;
declare variable SUMHUMWEIGHT integer;
declare variable AINVENTORYID integer;
declare variable HUMIDITYAFTERDRY numeric(4,2);
declare variable MAX_OWNERID integer;
declare variable THEORETICALHUMWEIGHT integer;
declare variable ALLHUMWEIGHT integer;
begin
  select coalesce(UWTYPES.METODSOLUTION,0)
    from UWTYPES
    where (UWTYPES.UWTYPEID = :UWTYPEID)
  into :METODSOLUTION;

  BEFOREWEIGHT = 0;
  RWEIGHT = 0;
  RBEGINDATE = UWACTDATE;
  RENDDATE = '01.01.2000';

  if (:UWTYPEID = 2) then
    OPERATIONID = 31;

  if (:UWTYPEID = 5) then
    OPERATIONID = 12;
    
  if (:FINALCALCULATION = 1) then begin
    delete from IT
      where (IT.OPERATIONID = 9) and (IT.UWORDERID = :UWORDERID) and (IT.STATE = -999);

    select QUALITY_SELECT.HUMIDITY
      from QUALITY_SELECT(:QUALITYAFTERID)
    into :HUMIDITYAFTERDRY;

    select UWORDERS.CLIENTID
      from UWORDERS
      where (UWORDERS.UWORDERID = :UWORDERID)
    into :OWNERID;
  end

/*
  select sum(IT.NET) WEIGHT, min(IT.REGDATE) MINDATE, max(IT.REGDATE) MAXDATE, min(IT.STORAGEID),
      sum(IT.NET * SQUALITY.DIRT) / nullif(sum(IT.NET + SQUALITY.DIRT - SQUALITY.DIRT),0),
      sum(IT.NET * SQUALITY.SCREENING) / nullif(sum(IT.NET + SQUALITY.SCREENING - SQUALITY.SCREENING),0),
      sum(IT.NET * SQUALITY.HUMIDITY) / nullif(sum(IT.NET + SQUALITY.HUMIDITY - SQUALITY.HUMIDITY),0),
      sum(IT.NET * SQUALITY.GRAINUNIT) / nullif(sum(IT.NET + SQUALITY.GRAINUNIT - SQUALITY.GRAINUNIT),0),
      sum(IT.NET * SQUALITY.SMALLGRAIN) / nullif(sum(IT.NET + SQUALITY.SMALLGRAIN - SQUALITY.SMALLGRAIN),0),
      sum(IT.NET * LQUALITY.DIRT) / nullif(sum(IT.NET + LQUALITY.DIRT - LQUALITY.DIRT),0),
      sum(IT.NET * LQUALITY.SCREENING) / nullif(sum(IT.NET + LQUALITY.SCREENING - LQUALITY.SCREENING),0),
      sum(IT.NET * LQUALITY.HUMIDITY) / nullif(sum(IT.NET + LQUALITY.HUMIDITY - LQUALITY.HUMIDITY),0),
      sum(IT.NET * LQUALITY.GRAINUNIT) / nullif(sum(IT.NET + LQUALITY.GRAINUNIT - LQUALITY.GRAINUNIT),0),
      sum(IT.NET * LQUALITY.SMALLGRAIN) / nullif(sum(IT.NET + LQUALITY.SMALLGRAIN - LQUALITY.SMALLGRAIN),0)
    from IT
      left join QUALITY_SELECT(IT.SAMPLERQUALITYID) SQUALITY on (0=0)
      left join QUALITY_SELECT(IT.LABQUALITYID) LQUALITY on (0=0)
    where (IT.OPERATIONID = :OPERATIONID)
      and (IT.REGDATE between :UWORDERDATE and :UWACTDATE)
      and (IT.UWORDERID = :UWORDERID)
      and ((IT.STATE > -1) and (IT.PARENTID <= 0))
  into :BEFOREWEIGHT, :RBEGINDATE, :RENDDATE, :STORAGEID,
       :DIRTBEFORE, :SCREENINGBEFORE, :HUMIDITYBEFORE, :GRAINUNITBEFORE, :SMALLGRAINBEFORE,
       :DIRTAFTER, :SCREENINGAFTER, :HUMIDITYAFTER, :GRAINUNITAFTER, :SMALLGRAINAFTER;
*/

  AQUALITYNUMBERBEFORE = null;
  AQUALITYNUMBERAFTER = null;

  if (:METODSOLUTION = 0) then begin
    for
      select IT.NET, IT.REGDATE, IT.PARTYID, IT.STORAGEID, IT.INVOICEID, IT.STORINGID, IT.OWNERID, IT.CONTRACTID,
          SQUALITY.CROPID, SQUALITY.CLASSID,
          SQUALITY.ANALYSISDATE, SQUALITY.QUALITYNUMBER, SQUALITY.DIRT, SQUALITY.SCREENING, SQUALITY.HUMIDITY, SQUALITY.GRAINUNIT, SQUALITY.SMALLGRAIN,
          LQUALITY.ANALYSISDATE, LQUALITY.QUALITYNUMBER, LQUALITY.DIRT, LQUALITY.SCREENING, LQUALITY.HUMIDITY, LQUALITY.GRAINUNIT, LQUALITY.SMALLGRAIN, LQUALITY.HARVESTYEAR
        from IT
          left join QUALITY_SELECT(IT.SAMPLERQUALITYID) SQUALITY on (0=0)
          left join QUALITY_SELECT(IT.LABQUALITYID) LQUALITY on (0=0)
        where (IT.OPERATIONID = :OPERATIONID)
          and (IT.REGDATE between :UWORDERDATE and :UWACTDATE)
          and (IT.UWORDERID = :UWORDERID)
          and ((IT.STATE > -1) and (IT.PARENTID <= 0))
      into :WEIGHTBEFOREDRYING, :REGDATE, :PARTYID, :STORAGEID, :INVOICEID, :ASTORINGID, :AOWNERID, :ACONTRACTID,
           :ACROPID, :ACLASSID,
           :ANALYSISDATEBEFORE, :QUALITYNUMBERBEFORE, :DIRTBEFORE, :SCREENINGBEFORE, :HUMIDITYBEFORE, :GRAINUNITBEFORE, :SMALLGRAINBEFORE,
           :ANALYSISDATEAFTER, :QUALITYNUMBERAFTER, :DIRTAFTER, :SCREENINGAFTER, :HUMIDITYAFTER, :GRAINUNITAFTER, :SMALLGRAINAFTER, :HARVESTYEAR

    do begin
      HUMWEIGHT = 0;

      if (AQUALITYNUMBERBEFORE is null) then
        AQUALITYNUMBERBEFORE = QUALITYNUMBERBEFORE;

      if (AQUALITYNUMBERAFTER is null) then
        AQUALITYNUMBERAFTER = QUALITYNUMBERAFTER;

      if (HUMIDITYBEFORE > HUMIDITYAFTER) then begin
        HUMWEIGHT = round(:WEIGHTBEFOREDRYING * cast((:HUMIDITYBEFORE - :HUMIDITYAFTER) * 100 / (100 - :HUMIDITYAFTER) as numeric(9,2)) / 100);
--        HUMWEIGHT = round(cast(:WEIGHTBEFOREDRYING as numeric(12,2)) * cast((:HUMIDITYBEFORE - :HUMIDITYAFTER) * 100 as numeric(9,2)) / (100 - :HUMIDITYAFTER) / 100);
--        HUMWEIGHT = cast((:HUMIDITYBEFORE - :HUMIDITYAFTER) * 100 / (100 - :HUMIDITYAFTER) as numeric(4,2)) * 10000;

        if (:FINALCALCULATION = 1) then begin
          select RESULTQUALITYID
            from QUALITY_INSERT(null, :ACROPID, :ACLASSID, :SORTID, :ANALYSISDATEBEFORE, :QUALITYNUMBERBEFORE,
                :HUMIDITYBEFORE, null, null, :HARVESTYEAR, :GRAINUNITBEFORE, null, null, :DIRTBEFORE, :SCREENINGBEFORE,
                null, :SMALLGRAINBEFORE, null, null, null)
          into :BEFOREDRYINGQUALITYID;

          select RESULTQUALITYID
            from QUALITY_INSERT(null, :ACROPID, :ACLASSID, :SORTID, :ANALYSISDATEAFTER, :QUALITYNUMBERAFTER,
                :HUMIDITYAFTER, null, null, :HARVESTYEAR, :GRAINUNITBEFORE, null, null, :DIRTAFTER, :SCREENINGAFTER,
                null, :SMALLGRAINAFTER, null, null, null)
            into :AFTERDRYINGQUALITYID;

          insert into IT (STORAGEID, GROSS, TARE, NET, UWORDERID,
              LABQUALITYID, SAMPLERQUALITYID, REGDATE, OPERATIONID, WEIGHT, STATE,
              OPERATIONPARAM, OWNERID, STORINGID, REGISTERID, PARTYID, INVOICEID, CONTRACTID)
            values (:STORAGEID, :WEIGHTBEFOREDRYING, :WEIGHTBEFOREDRYING - :HUMWEIGHT, :HUMWEIGHT, :UWORDERID,
                :AFTERDRYINGQUALITYID, :BEFOREDRYINGQUALITYID, :UWACTDATE, 9, :HUMWEIGHT, -999,
                :AFTERDRYINGQUALITYID, coalesce(:AOWNERID,:OWNERID), :STORINGID, :INVOICEID, :PARTYID, :INVOICEID, coalesce(:ACONTRACTID,:CONTRACTID));
        end
      end

      BEFOREWEIGHT = BEFOREWEIGHT + WEIGHTBEFOREDRYING;
      DIRT_BEFORE = DIRT_BEFORE + (DIRTBEFORE * WEIGHTBEFOREDRYING);
      SCREENING_BEFORE = SCREENING_BEFORE + (SCREENINGBEFORE * WEIGHTBEFOREDRYING);
      HUMIDITY_BEFORE = HUMIDITY_BEFORE + (HUMIDITYBEFORE * WEIGHTBEFOREDRYING);
      GRAINUNIT_BEFORE = GRAINUNIT_BEFORE + (GRAINUNITBEFORE * WEIGHTBEFOREDRYING);
      SMALLGRAIN_BEFORE = SMALLGRAIN_BEFORE + (SMALLGRAINBEFORE * WEIGHTBEFOREDRYING);

      DIRT_AFTER = DIRT_AFTER + (DIRTAFTER * WEIGHTBEFOREDRYING);
      SCREENING_AFTER = SCREENING_AFTER + (SCREENINGAFTER * WEIGHTBEFOREDRYING);
      HUMIDITY_AFTER = HUMIDITY_AFTER + (HUMIDITYAFTER * WEIGHTBEFOREDRYING);
      GRAINUNIT_AFTER = GRAINUNIT_AFTER + (GRAINUNITAFTER * WEIGHTBEFOREDRYING);
      SMALLGRAIN_AFTER = SMALLGRAIN_AFTER + (SMALLGRAINAFTER * WEIGHTBEFOREDRYING);

      RWEIGHT = RWEIGHT + (WEIGHTBEFOREDRYING - HUMWEIGHT);

      if ((REGDATE > RENDDATE) and (REGDATE <= UWACTDATE)) then
        RENDDATE = REGDATE;

      if ((REGDATE > UWORDERDATE) and (REGDATE <= RBEGINDATE)) then
        RBEGINDATE = REGDATE;
    end
  end
  else begin
    for
      select AIT.NET, AIT.REGDATE, AIT.PARTYID, AIT.STORAGEID, AIT.INVOICEID, AIT.STORINGID, AIT.OWNERID, AIT.CONTRACTID,
          SQUALITY.CROPID, SQUALITY.CLASSID,
          SQUALITY.ANALYSISDATE, SSQUALITY.QUALITYNUMBER, SQUALITY.DIRT, SQUALITY.SCREENING, SQUALITY.HUMIDITY, SQUALITY.GRAINUNIT, SQUALITY.SMALLGRAIN,
          LQUALITY.ANALYSISDATE, LQUALITY.QUALITYNUMBER, LQUALITY.DIRT, LQUALITY.SCREENING, LQUALITY.HUMIDITY, LQUALITY.GRAINUNIT, LQUALITY.SMALLGRAIN, LQUALITY.HARVESTYEAR
        from IT
          left join IT AIT on (AIT.RELATIONID = IT.INVENTORYID)
          left join QUALITY_SELECT(AIT.BUHQUALITYID) SQUALITY on (0=0)
          left join QUALITY_SELECT(IT.SAMPLERQUALITYID) SSQUALITY on (0=0)
          left join QUALITY_SELECT(IT.LABQUALITYID) LQUALITY on (0=0)
        where (IT.REGDATE between :UWORDERDATE and :UWACTDATE) and (IT.OPERATIONID = :OPERATIONID)
          and (IT.UWORDERID = :UWORDERID) and ((IT.STATE > -1) and (IT.PARENTID <= 0))
          and (AIT.NET is not null) and (AIT.SILAGEID = IT.SILAGEID)
      into :WEIGHTBEFOREDRYING, :REGDATE, :PARTYID, :STORAGEID, :INVOICEID, :ASTORINGID, :AOWNERID, :ACONTRACTID,
           :ACROPID, :ACLASSID,
           :ANALYSISDATEBEFORE, :QUALITYNUMBERBEFORE, :DIRTBEFORE, :SCREENINGBEFORE, :HUMIDITYBEFORE, :GRAINUNITBEFORE, :SMALLGRAINBEFORE,
           :ANALYSISDATEAFTER, :QUALITYNUMBERAFTER, :DIRTAFTER, :SCREENINGAFTER, :HUMIDITYAFTER, :GRAINUNITAFTER, :SMALLGRAINAFTER, :HARVESTYEAR

    do begin
      HUMWEIGHT = 0;

      if (AQUALITYNUMBERBEFORE is null) then
        AQUALITYNUMBERBEFORE = QUALITYNUMBERBEFORE;

      if (AQUALITYNUMBERAFTER is null) then
        AQUALITYNUMBERAFTER = QUALITYNUMBERAFTER;

      if (HUMIDITYBEFORE > HUMIDITYAFTER) then begin
        if (HUMIDITYAFTERDRY is not null) then begin
          HUMIDITYAFTER = HUMIDITYAFTERDRY;
          HUMWEIGHT = round(:WEIGHTBEFOREDRYING * cast((:HUMIDITYBEFORE - :HUMIDITYAFTER) * 100 / (100 - :HUMIDITYAFTER) as numeric(9,2)) / 100);
        end
        else HUMWEIGHT = round(:WEIGHTBEFOREDRYING * cast((:HUMIDITYBEFORE - :HUMIDITYAFTER) * 100 / (100 - :HUMIDITYAFTER) as numeric(9,2)) / 100);
--        HUMWEIGHT = round(cast(:WEIGHTBEFOREDRYING as numeric(12,2)) * cast((:HUMIDITYBEFORE - :HUMIDITYAFTER) * 100 as numeric(9,2)) / (100 - :HUMIDITYAFTER) / 100);
--        HUMWEIGHT = cast((:HUMIDITYBEFORE - :HUMIDITYAFTER) * 100 / (100 - :HUMIDITYAFTER) as numeric(4,2)) * 10000;

        if (:FINALCALCULATION = 1) then begin
          HUMIDITYAFTER = HUMIDITYAFTERDRY;
          HUMWEIGHT = round(:WEIGHTBEFOREDRYING * cast((:HUMIDITYBEFORE - :HUMIDITYAFTER) * 100 / (100 - :HUMIDITYAFTER) as numeric(9,2)) / 100);

          select RESULTQUALITYID
            from QUALITY_INSERT(null, :ACROPID, :ACLASSID, :SORTID, :ANALYSISDATEBEFORE, :QUALITYNUMBERBEFORE,
                :HUMIDITYBEFORE, null, null, :HARVESTYEAR, :GRAINUNITBEFORE, null, null, :DIRTBEFORE, :SCREENINGBEFORE,
                null, :SMALLGRAINBEFORE, null, null, null)
          into :BEFOREDRYINGQUALITYID;

          select RESULTQUALITYID
            from QUALITY_INSERT(null, :ACROPID, :ACLASSID, :SORTID, :ANALYSISDATEAFTER, :QUALITYNUMBERAFTER,
                :HUMIDITYAFTER, null, null, :HARVESTYEAR, :GRAINUNITBEFORE, null, null, :DIRTAFTER, :SCREENINGAFTER,
                null, :SMALLGRAINAFTER, null, null, null)
            into :AFTERDRYINGQUALITYID;

          insert into IT (STORAGEID, GROSS, TARE, NET, UWORDERID,
              LABQUALITYID, SAMPLERQUALITYID, REGDATE, OPERATIONID, WEIGHT, STATE,
              OPERATIONPARAM, OWNERID, STORINGID, REGISTERID, PARTYID, INVOICEID, CONTRACTID)
            values (:STORAGEID, :WEIGHTBEFOREDRYING, :WEIGHTBEFOREDRYING - :HUMWEIGHT, :HUMWEIGHT, :UWORDERID,
                :AFTERDRYINGQUALITYID, :BEFOREDRYINGQUALITYID, :UWACTDATE, 9, :HUMWEIGHT, -999,
                :AFTERDRYINGQUALITYID, coalesce(:AOWNERID,:OWNERID), :STORINGID, :INVOICEID, :PARTYID, :INVOICEID, coalesce(:ACONTRACTID,:CONTRACTID));
        end
      end

      BEFOREWEIGHT = BEFOREWEIGHT + WEIGHTBEFOREDRYING;
      DIRT_BEFORE = DIRT_BEFORE + (DIRTBEFORE * WEIGHTBEFOREDRYING);
      SCREENING_BEFORE = SCREENING_BEFORE + (SCREENINGBEFORE * WEIGHTBEFOREDRYING);
      HUMIDITY_BEFORE = HUMIDITY_BEFORE + (HUMIDITYBEFORE * WEIGHTBEFOREDRYING);
      GRAINUNIT_BEFORE = GRAINUNIT_BEFORE + (GRAINUNITBEFORE * WEIGHTBEFOREDRYING);
      SMALLGRAIN_BEFORE = SMALLGRAIN_BEFORE + (SMALLGRAINBEFORE * WEIGHTBEFOREDRYING);

      DIRT_AFTER = DIRT_AFTER + (DIRTAFTER * WEIGHTBEFOREDRYING);
      SCREENING_AFTER = SCREENING_AFTER + (SCREENINGAFTER * WEIGHTBEFOREDRYING);
      HUMIDITY_AFTER = HUMIDITY_AFTER + (HUMIDITYAFTER * WEIGHTBEFOREDRYING);
      GRAINUNIT_AFTER = GRAINUNIT_AFTER + (GRAINUNITAFTER * WEIGHTBEFOREDRYING);
      SMALLGRAIN_AFTER = SMALLGRAIN_AFTER + (SMALLGRAINAFTER * WEIGHTBEFOREDRYING);

      RWEIGHT = RWEIGHT + (WEIGHTBEFOREDRYING - HUMWEIGHT);

      if ((REGDATE > RENDDATE) and (REGDATE <= UWACTDATE)) then
        RENDDATE = REGDATE;

      if ((REGDATE > UWORDERDATE) and (REGDATE <= RBEGINDATE)) then
        RBEGINDATE = REGDATE;
    end
  end

  if (AQUALITYNUMBERBEFORE <> QUALITYNUMBERBEFORE) then
    QUALITYNUMBERBEFORE = null;

  if (AQUALITYNUMBERAFTER <> QUALITYNUMBERAFTER) then
    QUALITYNUMBERAFTER = null;

  if (BEFOREWEIGHT = 0) then begin
    suspend;
    exit;
  end
  
--  DIRTBEFORE = DIRT_BEFORE / BEFOREWEIGHT;
--  SCREENINGBEFORE = SCREENING_BEFORE / BEFOREWEIGHT;
--  HUMIDITYBEFORE = HUMIDITY_BEFORE / BEFOREWEIGHT;

  if (:METODSOLUTION = 0) then begin
    DIRTBEFORE = cast(cast(DIRT_BEFORE as numeric (15,4)) / BEFOREWEIGHT as numeric (15,2)); -- Зерновая примесь
    SCREENINGBEFORE = cast(cast(SCREENING_BEFORE as numeric (15,4)) / BEFOREWEIGHT as numeric (15,2)); -- Зерновая примесь
    HUMIDITYBEFORE = cast(cast(HUMIDITY_BEFORE as numeric (15,4)) / BEFOREWEIGHT as numeric (15,2)); --Влажность
    GRAINUNITBEFORE = GRAINUNIT_BEFORE / BEFOREWEIGHT;
    SMALLGRAINBEFORE = SMALLGRAIN_BEFORE / BEFOREWEIGHT;
  end
  else begin
    select
        cast(sum(cast(QUALITY_SELECT.DIRT as numeric(15,4)) * AIT.NET) / sum(AIT.NET) as numeric(4,2)),
        cast(sum(cast(QUALITY_SELECT.SCREENING as numeric(15,4)) * AIT.NET) / sum(AIT.NET) as numeric(4,2)),
        cast(sum(cast(QUALITY_SELECT.HUMIDITY as numeric(15,4)) * AIT.NET) / sum(AIT.NET) as numeric(4,2)),
        cast(sum(cast(QUALITY_SELECT.GRAINUNIT as numeric(15,4)) * AIT.NET) / sum(AIT.NET) as integer),
        cast(sum(cast(QUALITY_SELECT.SMALLGRAIN as numeric(15,4)) * AIT.NET) / sum(AIT.NET) as numeric(4,2))
      from IT
        left join IT AIT on (AIT.RELATIONID = IT.INVENTORYID)
        left join QUALITY_SELECT(AIT.LABQUALITYID) on (0=0)
      where (IT.OPERATIONID = :OPERATIONID)
        and (IT.REGDATE between :UWORDERDATE and :UWACTDATE)
        and (IT.UWORDERID = :UWORDERID)
        and ((IT.STATE > -1) and (IT.PARENTID <= 0))
        and (AIT.NET is not null) and (AIT.SILAGEID = IT.SILAGEID)
    into :DIRTBEFORE, :SCREENINGBEFORE, :HUMIDITYBEFORE, :GRAINUNITBEFORE, :SMALLGRAINBEFORE;
  end

  execute procedure QUALITY_UPDATE(0, :QUALITYBEFOREID, :CROPID, :CLASSID, :SORTID,  null,
      :QUALITYNUMBERBEFORE, :HUMIDITYBEFORE, null, null, null, :GRAINUNITBEFORE, null, null,
      :DIRTBEFORE, :SCREENINGBEFORE, null, :SMALLGRAINBEFORE, null, null)
  returning_values :QUALITYBEFOREID;

  DIRTAFTER = DIRT_AFTER / BEFOREWEIGHT;
  SCREENINGAFTER = SCREENING_AFTER / BEFOREWEIGHT;

  if ((:FINALCALCULATION = 0) or (:METODSOLUTION = 0)) then
    HUMIDITYAFTER = HUMIDITY_AFTER / BEFOREWEIGHT;
  else HUMIDITYAFTER = HUMIDITYAFTERDRY;

  GRAINUNITAFTER = GRAINUNIT_AFTER / BEFOREWEIGHT;
  SMALLGRAINAFTER = SMALLGRAIN_AFTER / BEFOREWEIGHT;

  execute procedure QUALITY_UPDATE(0, :QUALITYAFTERID, :CROPID, :CLASSID, :SORTID, null,
      :QUALITYNUMBERAFTER, :HUMIDITYAFTER, null, null, null, :GRAINUNITAFTER, null, null,
      :DIRTAFTER, :SCREENINGAFTER, null, :SMALLGRAINAFTER, null, null)
  returning_values :QUALITYAFTERID;

/*
  if (:UWTYPEID = 2) then begin
    DRYING = cast((:HUMIDITYBEFORE - :HUMIDITYAFTER) * 100 as numeric(9,5)) / (100 - :HUMIDITYAFTER);
--    BEFOREWEIGHT = cast((:RWEIGHT / (1 - cast(:DRYING as numeric(9, 5)) / 100)) as integer);
    RWEIGHT = :BEFOREWEIGHT - (:BEFOREWEIGHT * DRYING / 100);
  end

  if (:UWTYPEID = 5) then begin
    DRYING = null;
    RWEIGHT = :BEFOREWEIGHT;
  end
*/

  DRYING = cast((:HUMIDITYBEFORE - :HUMIDITYAFTER) * 100 as numeric(9,5)) / (100 - :HUMIDITYAFTER);
--  RWEIGHT = :BEFOREWEIGHT - (:BEFOREWEIGHT * DRYING / 100);

  SETTLEMENTSHRINKAGEWEIGHT = BEFOREWEIGHT * DRYING / 100;

  if (:FINALCALCULATION = 1) then begin
    if (:METODSOLUTION = 0) then begin
      select max(IT.INVENTORYID), sum(IT.GROSS), sum(IT.NET)
        from IT
        where (IT.UWORDERID = :UWORDERID) and (IT.NET > 0)
          and (IT.STATE = -999) and (IT.OPERATIONID = 9)
      group by IT.UWORDERID
      into :INVENTORYID, :ALLWEIGHTBEFOREDRYING, :SHRINKAGEWEIGHT;

      if ((SHRINKAGEWEIGHT > 0) and (SHRINKAGEWEIGHT <> SETTLEMENTSHRINKAGEWEIGHT)) then begin
        COEFFICIENT = cast((cast(SETTLEMENTSHRINKAGEWEIGHT as numeric (15,8)) / SHRINKAGEWEIGHT) as numeric (9,8));
        SUMHUMWEIGHT = 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)
          order by IT.INVENTORYID
          into :AINVENTORYID, :RWEIGHT

        do begin
          if (AINVENTORYID <> INVENTORYID) then begin
            HUMWEIGHT = round(RWEIGHT * COEFFICIENT);
            SUMHUMWEIGHT = SUMHUMWEIGHT + HUMWEIGHT;
          end
          else HUMWEIGHT = SETTLEMENTSHRINKAGEWEIGHT - SUMHUMWEIGHT;

          if (HUMWEIGHT <> RWEIGHT) then
            update IT
              set NET = :HUMWEIGHT
            where (INVENTORYID = :AINVENTORYID);
        end
      end
    end
    else begin
      select max(OWNERID), sum(DRYWEIGHT), sum(DRYINGWEIGHT)
        from (
          select OWNERID, BEFOREDRYWEIGHT, DRYWEIGHT,
              cast(BEFOREDRYWEIGHT * 100 * (BEFOREDRYHUMIDITY - AFTERDRYHUMIDITY) / (100 - AFTERDRYHUMIDITY) / 100 as integer) DRYINGWEIGHT
            from (
              select OWNERID, sum(GROSS) BEFOREDRYWEIGHT, sum(NET) DRYWEIGHT,
                  cast(cast(sum(cast(BEFOREDRYHUMIDITY as numeric(4,2)) * GROSS) as numeric (12,6)) / sum(GROSS) as numeric (4,2)) BEFOREDRYHUMIDITY,
                  max(AFTERDRYHUMIDITY) AFTERDRYHUMIDITY
                from (
                    select IT.OWNERID, IT.GROSS, IT.NET,
                        BEFOREQ.HUMIDITY BEFOREDRYHUMIDITY, AFTERQ.HUMIDITY AFTERDRYHUMIDITY
                      from IT
                        left join QUALITY_SELECT(IT.SAMPLERQUALITYID) BEFOREQ on (0=0)
                        left join QUALITY_SELECT(IT.OPERATIONPARAM) AFTERQ on (0=0)
                      where (IT.UWORDERID = :UWORDERID) and (IT.STATE = -999) and (IT.OPERATIONID = 9)
                  order by IT.OWNERID
                )
              group by OWNERID
            )
        )
      into :MAX_OWNERID, :SHRINKAGEWEIGHT, :THEORETICALSHRINKAGEWEIGHT;

      if ((SHRINKAGEWEIGHT > 0) and (SHRINKAGEWEIGHT <> SETTLEMENTSHRINKAGEWEIGHT)) then begin
        COEFFICIENT_ALL = cast((cast(SETTLEMENTSHRINKAGEWEIGHT as numeric (15,8)) / THEORETICALSHRINKAGEWEIGHT) as numeric (9,8));
        SUMHUMWEIGHT_ALL = 0;

        for
          select OWNERID, max(INVENTORYID), sum(GROSS), sum(NET),
              cast(cast(sum(cast(BEFOREDRYHUMIDITY as numeric(4,2)) * GROSS) as numeric (12,6)) / sum(GROSS) as numeric (4,2)) BEFOREDRYHUMIDITY
            from (
                select IT.OWNERID, IT.INVENTORYID, IT.GROSS, IT.NET,
                    BEFOREQ.HUMIDITY BEFOREDRYHUMIDITY, AFTERQ.HUMIDITY AFTERDRYHUMIDITY
                  from IT
                    left join QUALITY_SELECT(IT.SAMPLERQUALITYID) BEFOREQ on (0=0)
                    left join QUALITY_SELECT(IT.OPERATIONPARAM) AFTERQ on (0=0)
                  where (IT.UWORDERID = :UWORDERID) and (IT.STATE = -999) and (IT.OPERATIONID = 9)
              order by IT.OWNERID
            )
          group by OWNERID
          into :OWNERID, :INVENTORYID, :WEIGHTBEFOREDRYING, :SHRINKAGEWEIGHT, :HUMIDITYBEFORE
        do begin
          THEORETICALHUMWEIGHT = cast(WEIGHTBEFOREDRYING * 100 * (HUMIDITYBEFORE - HUMIDITYAFTER) / (100 - HUMIDITYAFTER) / 100 as integer);
          COEFFICIENT = cast((cast(THEORETICALHUMWEIGHT as numeric (15,8)) / SHRINKAGEWEIGHT) * COEFFICIENT_ALL as numeric (9,8));
          ALLHUMWEIGHT = cast(SHRINKAGEWEIGHT * COEFFICIENT as integer);
          SUMHUMWEIGHT = 0;

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

          do begin
            if (AINVENTORYID <> INVENTORYID) then begin
              HUMWEIGHT = round(RWEIGHT * COEFFICIENT);
              SUMHUMWEIGHT = SUMHUMWEIGHT + HUMWEIGHT;
            end
            else
              if (OWNERID <> MAX_OWNERID) then
                HUMWEIGHT = ALLHUMWEIGHT - SUMHUMWEIGHT;
              else HUMWEIGHT = SETTLEMENTSHRINKAGEWEIGHT - SUMHUMWEIGHT_ALL;

            SUMHUMWEIGHT_ALL = SUMHUMWEIGHT_ALL + HUMWEIGHT;

            if (HUMWEIGHT <> RWEIGHT) then
              update IT
                set TARE = GROSS - :HUMWEIGHT,
                    NET = :HUMWEIGHT
              where (INVENTORYID = :AINVENTORYID);
          end
        end
      end
    end
  end

  update UWORDERS
  set BEFOREDRYINGWEIGHT = :BEFOREWEIGHT,
      BEFOREDRYQUALITYID = :QUALITYBEFOREID,
      PARTYID = :PARTYID
  where (UWORDERID = :UWORDERID);

  update UWACTS
  set WEIGHT = :BEFOREWEIGHT - :SETTLEMENTSHRINKAGEWEIGHT,
--      BEGINDATE = :RBEGINDATE,
--      ENDDATE = :RENDDATE,
      DRYING = :DRYING,
      AFTERDRYINGWEIGHT = :BEFOREWEIGHT - :SETTLEMENTSHRINKAGEWEIGHT,
      AFTERDRYQUALITYID = :QUALITYAFTERID
  where UWORDERID = :UWORDERID;

  if (:UWTYPEID = 2) then begin
    update IT
    set STORAGEID = :STORAGEID,
--        NET = 0,
        UWORDERID = :UWORDERID,
--        LABQUALITYID = :QUALITYAFTERID,
--        SAMPLERQUALITYID = :QUALITYBEFOREID,
        REGDATE = :UWACTDATE,
        OPERATIONID = 9,
        STORINGID = :STORINGID
--        WEIGHT = :RWEIGHT - :BEFOREWEIGHT
    where OPERATIONID = 9 and UWORDERID = :UWORDERID;

    if (row_count = 0) then
      insert into IT (STORAGEID, NET, UWORDERID, LABQUALITYID, SAMPLERQUALITYID, REGDATE, OPERATIONID, WEIGHT, STATE, STORINGID)
        values (:STORAGEID, 0, :UWORDERID, :QUALITYAFTERID, :QUALITYBEFOREID, :UWACTDATE, 9, :RWEIGHT - :BEFOREWEIGHT, 3, :STORINGID);
  end

  suspend;

end