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

UNDERWORK_CALCUWACT_GRAINOUT

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

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

Описание

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

Определение

CREATE PROCEDURE UNDERWORK_CALCUWACT_GRAINOUT(
    UWORDERDATE DATE,
    UWACTDATE DATE,
    UWORDERID INTEGER,
    QUALITYBEFOREID INTEGER,
    QUALITYAFTERID INTEGER,
    CROPID INTEGER,
    CLASSID INTEGER,
    STORAGE_IN INTEGER,
    UWTYPEID INTEGER,
    CLIENTID INTEGER,
    SORTID INTEGER,
    FINALCALCULATION INTEGER)
RETURNS (
    BEFOREWEIGHT INTEGER,
    STORAGEID INTEGER)
AS
declare variable CROPTYPEID integer;
declare variable FULLWEIGHT double precision;
declare variable RWEIGHT double precision;
declare variable QUALITYNUMBERBEFORE integer;
declare variable QUALITYNUMBERAFTER integer;
declare variable HUMIDITYBEFORE numeric(9,2);
declare variable HUMIDITYAFTER numeric(9,2);
declare variable DIRTBEFORE numeric(9,2);
declare variable DIRTAFTER numeric(9,2);
declare variable SCREENINGBEFORE numeric(9,2);
declare variable SCREENINGAFTER numeric(9,2);
declare variable OILSEEDBEFORE numeric(9,2);
declare variable OILSEEDAFTER numeric(9,2);
declare variable GRAINUNITBEFORE integer;
declare variable GRAINUNITAFTER integer;
declare variable SMALLGRAINBEFORE numeric(9,2);
declare variable SMALLGRAINAFTER numeric(9,2);
declare variable AWEIGHT double precision;
declare variable ACROPID integer;
declare variable ACLASSID integer;
declare variable AQUALITYNUMBER integer;
declare variable AHUMIDITY numeric(4,2);
declare variable ADIRT numeric(4,2);
declare variable ASCREENING decimal(4,2);
declare variable AOILSEED numeric(4,2);
declare variable AGRAINUNIT integer;
declare variable ASMALLGRAIN numeric(4,2);
declare variable W_FORAGE1C_A double precision;
declare variable W_FORAGE1C_B double precision;
declare variable W_FORAGE2C double precision;
declare variable W_FORAGE3C double precision;
declare variable W_BYPRODUCT_A double precision;
declare variable W_BYPRODUCT_B double precision;
declare variable W_SMALLGRAIN double precision;
declare variable Q_FORAGE1C_A integer;
declare variable Q_FORAGE1C_B integer;
declare variable Q_FORAGE2C integer;
declare variable Q_FORAGE3C integer;
declare variable Q_BYPRODUCT_A integer;
declare variable Q_BYPRODUCT_B integer;
declare variable Q_SMALLGRAIN integer;
declare variable FORAGEQUALITYNUMBER integer;
declare variable FORAGEHUMIDITY numeric(9,2);
declare variable FORAGEDIRT numeric(9,2);
declare variable FORAGESCREENING numeric(9,2);
declare variable FORAGEGRAINUNIT integer;
declare variable FORAGESMALLGRAIN numeric(9,2);
declare variable RBEGINDATE date;
declare variable RENDDATE date;
declare variable METODSELECTIONWASTE smallint;
declare variable Q_WASTE integer;
declare variable DRYING numeric(9,2);
declare variable CLEAN numeric(9,2);
begin

  select CROP.CROPTYPEID
    from CROP
    where (CROP.CROPID = :CROPID)
  into :CROPTYPEID;

  RWEIGHT = 0;
  DRYING = 0;
  CLEAN = 0;
  RBEGINDATE = null;
  RENDDATE = null;
  STORAGEID = :STORAGE_IN;

  select sum(IT.NET), min(IT.REGDATE), max(IT.REGDATE),
      sum(IT.NET * QUALITY_SELECT.HUMIDITY) / nullif(sum(IT.NET - QUALITY_SELECT.HUMIDITY + QUALITY_SELECT.HUMIDITY), 0),
      sum(IT.NET * QUALITY_SELECT.DIRT) / nullif(sum(IT.NET), 0),
      sum(IT.NET * QUALITY_SELECT.SCREENING) / nullif(sum(IT.NET - QUALITY_SELECT.SCREENING + QUALITY_SELECT.SCREENING), 0),
      sum(IT.NET * QUALITY_SELECT.OILSEED) / nullif(sum(IT.NET), 0),
      sum(IT.NET * QUALITY_SELECT.GRAINUNIT) / nullif(sum(IT.NET - QUALITY_SELECT.GRAINUNIT + QUALITY_SELECT.GRAINUNIT), 0),
      sum(IT.NET * QUALITY_SELECT.SMALLGRAIN) / nullif(sum(IT.NET), 0)
    from IT
      left join UWOUTACTS on (UWOUTACTS.INVOICEID = IT.INVOICEID)
      left join QUALITY_SELECT(IT.LABQUALITYID) on (0 = 0)
    where (IT.UWORDERID = :UWORDERID)
      and (IT.REGDATE between :UWORDERDATE and :UWACTDATE)
      and ((IT.OPERATIONID in (5, 7, 29, 30, 31)) and (IT.STATE > -1) and (IT.PARENTID <= 0))
  into :BEFOREWEIGHT, :RBEGINDATE, :RENDDATE,
       :HUMIDITYBEFORE, :DIRTBEFORE, :SCREENINGBEFORE, :OILSEEDBEFORE, :GRAINUNITBEFORE, :SMALLGRAINBEFORE;

  select sum(IT.NET),
      sum(IT.NET * QUALITY_SELECT.HUMIDITY) / nullif(sum(IT.NET), 0),
      sum(IT.NET * QUALITY_SELECT.DIRT) / nullif(sum(IT.NET), 0),
      sum(IT.NET * QUALITY_SELECT.SCREENING) / nullif(sum(IT.NET), 0),
      sum(IT.NET * QUALITY_SELECT.OILSEED) / nullif(sum(IT.NET), 0),
      sum(IT.NET * QUALITY_SELECT.GRAINUNIT) / nullif(sum(IT.NET), 0),
      sum(IT.NET * QUALITY_SELECT.SMALLGRAIN) / nullif(sum(IT.NET), 0)
    from IT
      left join UWOUTACTS on (UWOUTACTS.INVOICEID = IT.INVOICEID)
      left join QUALITY_SELECT(IT.LABQUALITYID) on (0 = 0)
    where (IT.UWORDERID = :UWORDERID)
      and (IT.REGDATE between :UWORDERDATE and :UWACTDATE)
      and ((IT.OPERATIONID in (5, 7, 29, 30, 31)) and (IT.STATE > -1) and (IT.PARENTID <= 0))
      and (QUALITY_SELECT.CLASSID not in (41))
  into :RWEIGHT,
       :HUMIDITYAFTER, :DIRTAFTER, :SCREENINGAFTER, :OILSEEDAFTER, :GRAINUNITAFTER, :SMALLGRAINAFTER;

/* ----------------------  */

  if (:finalcalculation = 1) then begin
    select QUALITYNUMBER, HUMIDITY, DIRT, SCREENING, OILSEED, GRAINUNIT, SMALLGRAIN
      from QUALITY_SELECT(:QUALITYBEFOREID)
    into :AQUALITYNUMBER, :AHUMIDITY, :ADIRT, :ASCREENING, :AOILSEED, :AGRAINUNIT, :ASMALLGRAIN;
    if (AQUALITYNUMBER is not null) then
      QUALITYNUMBERBEFORE = :AQUALITYNUMBER;
    if (AHUMIDITY is not null) then
      HUMIDITYBEFORE = :AHUMIDITY;
    if (ADIRT is not null) then
      DIRTBEFORE = :ADIRT;
    if (ASCREENING is not null) then
      SCREENINGBEFORE = :ASCREENING;
    if (AOILSEED is not null) then
      OILSEEDBEFORE = :AOILSEED;
    if (AGRAINUNIT is not null) then
      GRAINUNITBEFORE = :AGRAINUNIT;
    if (ASMALLGRAIN is not null) then
      SMALLGRAINBEFORE = :ASMALLGRAIN;

    select QUALITYNUMBER, HUMIDITY, DIRT, SCREENING, OILSEED, GRAINUNIT, SMALLGRAIN
      from QUALITY_SELECT(:QUALITYAFTERID)
    into :AQUALITYNUMBER, :AHUMIDITY, :ADIRT, :ASCREENING, :AOILSEED, :AGRAINUNIT, :ASMALLGRAIN;
    if (AQUALITYNUMBER is not null) then
      QUALITYNUMBERAFTER = :AQUALITYNUMBER;
    if (AHUMIDITY is not null) then
      HUMIDITYAFTER = :AHUMIDITY;
    if (ADIRT is not null) then
      DIRTAFTER = :ADIRT;
    if (ASCREENING is not null) then
      SCREENINGAFTER = :ASCREENING;
    if (AOILSEED is not null) then
      OILSEEDAFTER = :AOILSEED;
    if (AGRAINUNIT is not null) then
      GRAINUNITAFTER = :AGRAINUNIT;
    if (ASMALLGRAIN is not null) then
      SMALLGRAINAFTER = :ASMALLGRAIN;
  end

  if (DIRTBEFORE > DIRTAFTER) then
    CLEAN = :DIRTBEFORE - :DIRTAFTER;

  if (:CROPTYPEID = 5) then begin
    SCREENINGBEFORE = :OILSEEDBEFORE;
    SCREENINGAFTER = :OILSEEDAFTER;
  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;

  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;

  W_FORAGE1C_A = 0;
  W_FORAGE1C_A = 0;
  W_FORAGE2C = 0;
  W_FORAGE3C = 0;
  W_SMALLGRAIN = 0;
  W_BYPRODUCT_A = 0;
  W_BYPRODUCT_A = 0;

  for
    select QUALITY_SELECT.CLASSID, min(QUALITY_SELECT.CROPID), sum(IT.NET),
        sum(IT.NET * QUALITY_SELECT.HUMIDITY) / nullif(sum(IT.NET), 0),
        sum(IT.NET * QUALITY_SELECT.DIRT) / nullif(sum(IT.NET), 0),
        sum(IT.NET * QUALITY_SELECT.SCREENING) / nullif(sum(IT.NET), 0),
        sum(IT.NET * QUALITY_SELECT.GRAINUNIT) / nullif(sum(IT.NET), 0),
        sum(IT.NET * QUALITY_SELECT.SMALLGRAIN) / nullif(sum(IT.NET), 0)
      from IT
        left join QUALITY_SELECT(IT.LABQUALITYID) on (0 = 0)
      where (IT.REGDATE between :UWORDERDATE and :UWACTDATE)
        and (IT.OPERATIONID in (5, 14))
        and (IT.STORAGEID = :STORAGEID)
        and (IT.STATE > -1)
        and (IT.PARENTID <= 0)
        and (IT.UWORDERID = :UWORDERID)
      group by QUALITY_SELECT.CLASSID
    into :ACLASSID, :ACROPID, :AWEIGHT,
         :FORAGEHUMIDITY, :FORAGEDIRT, :FORAGESCREENING, :FORAGEGRAINUNIT, :FORAGESMALLGRAIN
  do begin
    if (:ACLASSID = 41) then begin
      W_FORAGE3C = :AWEIGHT;
--      select resultqualityid from quality_insert(null, null, 41, null, null, null, :ForageHumidity, null, null, null, :ForageGrainUnit, null, null, :ForageDirt, :ForageScreening, null, :ForageSmallGrain, null, null, null)
--      into :Q_FORAGE3C;
      select UWACTS.QUALITYID_3C
        from UWACTS
        where (UWACTS.UWORDERID = :UWORDERID)
      into :Q_FORAGE3C;

      if (Q_FORAGE3C is null) then
        select RESULTQUALITYID
          from QUALITY_INSERT(null, null, 41, null, null, null, :FORAGEHUMIDITY, null, null, null,
                              :FORAGEGRAINUNIT, null, null, :FORAGEDIRT, :FORAGESCREENING, null,
                              :FORAGESMALLGRAIN, null, null, null)
        into :Q_FORAGE3C;
      else begin
        if (:finalcalculation = 1) then begin
          select QUALITYNUMBER, HUMIDITY, DIRT, SCREENING, GRAINUNIT, SMALLGRAIN
            from QUALITY_SELECT(:Q_FORAGE3C)
          into :AQUALITYNUMBER, :AHUMIDITY, :ADIRT, :ASCREENING, :AGRAINUNIT, :ASMALLGRAIN;
          if (AQUALITYNUMBER is not null) then
            FORAGEQUALITYNUMBER = :AQUALITYNUMBER;
          if (AHUMIDITY is not null) then
            FORAGEHUMIDITY = :AHUMIDITY;
          if (ADIRT is not null) then
            FORAGEDIRT = :ADIRT;
          if (ASCREENING is not null) then
            FORAGESCREENING = :ASCREENING;
          if (AGRAINUNIT is not null) then
            FORAGEGRAINUNIT = :AGRAINUNIT;
          if (ASMALLGRAIN is not null) then
            FORAGESMALLGRAIN = :ASMALLGRAIN;
        end

        execute procedure QUALITY_UPDATE(0, :Q_FORAGE3C, ACROPID, :ACLASSID, null, null, :FORAGEQUALITYNUMBER,
          :FORAGEHUMIDITY, null, null, null, :FORAGEGRAINUNIT, null, null, :FORAGEDIRT, :FORAGESCREENING, null,
          :FORAGESMALLGRAIN, null, null)
        returning_values :Q_FORAGE3C;
      end

    end
  end
/*
  if (:metodselectionwaste = 0) then begin
    select sum(IT.NET)
    from IT
      left join uwoutacts on (IT.invoiceid = uwoutacts.invoiceid)
    where IT.REGDATE between :UWORDERDATE and :UWACTDATE
      and it.operationid in (7, 29, 30, 31)
      and it.storageid = :storageid
      and uwoutacts.invoiceid is not null
      and it.state > -1 and it.parentid <= 0
    into :FULLWEIGHT;

    W_Forage1C_a = cast((:RWEIGHT * :W_Forage1C_a / :FULLWEIGHT) as integer);
    W_Forage1C_b = cast((:RWEIGHT * :W_Forage1C_b / :FULLWEIGHT) as integer);
    W_Forage2C = cast((:RWEIGHT * :W_Forage2C / :FULLWEIGHT) as integer);
    W_Forage3C = cast((:RWEIGHT * :W_Forage3C / :FULLWEIGHT) as integer);
    W_SmallGrain = cast((:RWEIGHT * :W_SmallGrain / :FULLWEIGHT) as integer);
    W_ByProduct_a = cast((:RWEIGHT * :W_ByProduct_a / :FULLWEIGHT) as integer);
    W_ByProduct_b = cast((:RWEIGHT * :W_ByProduct_b / :FULLWEIGHT) as integer);
  end
*/

  update uwacts
  set WEIGHT = :RWEIGHT,
      BEGINDATE = :RBEGINDATE,
      ENDDATE = :RENDDATE,
      FORAGE1C_A = nullif(:W_FORAGE1C_A, 0),
      FORAGE1C_B = nullif(:W_FORAGE1C_B, 0),
      FORAGE2C = nullif(:W_FORAGE2C, 0),
      FORAGE3C = nullif(:W_FORAGE3C, 0),
      FORAGE_SMALLGRAIN = nullif(:W_SMALLGRAIN, 0),
      FORAGE_BY_PRODUCT_A = nullif(:W_BYPRODUCT_A, 0),
      FORAGE_BY_PRODUCT_B = nullif(:W_BYPRODUCT_B, 0),

      QUALITYID_1C_A = :Q_FORAGE1C_A,
      QUALITYID_1C_b = :Q_FORAGE1C_B,
      QUALITYID_2C = :Q_FORAGE2C,
      QUALITYID_3C = :Q_FORAGE3C,
      QUALITYID_SMALLGRAIN = :Q_SMALLGRAIN,
      QUALITYID_BY_PRODUCT_A = :Q_BYPRODUCT_A,
      QUALITYID_BY_PRODUCT_B = :Q_BYPRODUCT_B,
      DRYING = :DRYING,
      CLEAN = :CLEAN
  where (UWORDERID = :UWORDERID);

  suspend;

end