"localhost:/firebird/data/ILICHEVSK2.FDB". Триггеры

UWOUTACTS_SETITQUALITY

Описание

Пересчет качества в IT при подработке

Определение

CREATE TRIGGER UWOUTACTS_SETITQUALITY FOR UWOUTACTS
ACTIVE AFTER UPDATE POSITION 0
AS
declare variable LabQualityID integer;
declare variable SamplerQualityID integer;
declare variable InventoryID integer;
declare variable RecipeID integer;
declare variable ClassID integer;
declare variable CropID integer;
declare variable SortID integer;

declare variable Dirt numeric(9, 2);
declare variable DirtSum numeric(9, 2);
declare variable DirtBefore numeric(9, 2);
declare variable DirtAfter numeric(9, 2);
declare variable DirtBeforeAnaliz numeric(9, 2);

declare variable Screening numeric(9, 2);
declare variable ScreeningSum numeric(9, 2);
declare variable ScreeningBefore numeric(9, 2);
declare variable ScreeningAfter numeric(9, 2);
declare variable ScreeningBeforeAnaliz numeric(9, 2);

declare variable HumidityAfter numeric (9, 2);
declare variable GrainUnitAfter numeric (9, 2);
declare variable SmallGrainAfter numeric (9, 2);
declare variable HumidityBefore numeric (9, 2);
declare variable GrainUnitBefore numeric (9, 2);
declare variable SmallGrainBefore numeric (9, 2);

declare variable AnalisysNumber integer;
declare variable DirtAfterAnalise numeric (9, 2);
declare variable ScreeningfterAnalise numeric (9, 2);
begin
  if ((new.afterqualityid is not null)
      and (new.beforequalityid is not null)
      and (new.recipeid is not null)
     )
  then begin
    for
      select it.inventoryid, it.operationparam, it.relationid, quality.sortid
      from it
        left join quality on (it.samplerqualityid = quality.qualityid)
      where it.invoiceid = new.invoiceid
      into :inventoryid, :classid, :recipeid, :sortid
    do begin
      select Cropid from class where classid = :classid into :cropid;
/*  Определение DirtClass  */
      select sum(recipesilages.percentweight * quality_select.dirt) / nullif(sum(recipesilages.percentweight + quality_select.dirt - quality_select.dirt), 0),
             sum(recipesilages.percentweight * quality_select.screening) / nullif(sum(recipesilages.percentweight + quality_select.screening - quality_select.screening), 0)
      from recipesilages
        left join Silagepacks on (Silagepacks.silagepackid = recipesilages.silagepackid)
        left join quality_select(recipesilages.qualityid) on (0 = 0)
      where recipesilages.recipeid = :recipeid
        and coalesce(quality_select.classid, -1) = coalesce(:ClassID, -1)
        and coalesce(quality_select.sortid, -1) = coalesce(:SortID, -1)
      group by quality_select.classid
      into :Dirt, :screening;
/*  oпределение DirtSum  */
      select
        sum(recipequality.dirt * (select sum(it.net)
                                  from it left join quality itquality on it.samplerqualityid = itquality.qualityid
                                  where it.invoiceid = new.invoiceid
                                    and coalesce(it.operationparam, -1) = coalesce(recipequality.classid, -1)
                                    and coalesce(recipequality.sortid, -1) = coalesce(itquality.sortid, -1)
                                  )
           )
        / nullif(sum((select sum(it.net)
              from it left join quality itquality on it.samplerqualityid = itquality.qualityid
              where it.invoiceid = new.invoiceid
                and coalesce(it.operationparam, -1) = coalesce(recipequality.classid, -1)
                and coalesce(recipequality.sortid, -1) = coalesce(itquality.sortid, -1)
              ) - recipequality.dirt + recipequality.dirt
             ), 0),
        sum(recipequality.screening * (select sum(it.net)
                                       from it left join quality itquality on it.samplerqualityid = itquality.qualityid
                                       where it.invoiceid = new.invoiceid
                                         and coalesce(it.operationparam, -1) = coalesce(recipequality.classid, -1)
                                         and coalesce(recipequality.sortid, -1) = coalesce(itquality.sortid, -1)
                                       )
           )
        / nullif(sum((select sum(it.net)
               from it left join quality itquality on it.samplerqualityid = itquality.qualityid
               where it.invoiceid = new.invoiceid
                 and coalesce(it.operationparam, -1) = coalesce(recipequality.classid, -1)
                 and coalesce(recipequality.sortid, -1) = coalesce(itquality.sortid, -1)
              ) - recipequality.screening + recipequality.screening
             ), 0)
      from recipesilages
        left join quality_select(recipesilages.qualityid) recipequality on 0 = 0
      where recipesilages.recipeid = :recipeid
      into :dirtsum, :screeningsum;

/*  определение DirtAnaliz  */
      select quality_select.Dirt, quality_select.screening, quality_select.humidity, quality_select.grainunit, quality_select.smallgrain
      from quality_select(new.beforequalityid)
      into :DirtBeforeAnaliz, :ScreeningBeforeAnaliz, :HumidityBefore, :GrainUnitBefore, :SmallGrainBefore;
/*  определения DirtAfterAnalize  */
      select quality_select.Dirt, quality_select.screening, quality_select.qualitynumber, quality_select.humidity, quality_select.GrainUnit, quality_select.smallgrain
      from quality_select(new.afterqualityid)
      into :DirtAfterAnalise, :ScreeningfterAnalise, :analisysnumber, :HumidityAfter, :GrainUnitAfter, :SmallGrainAfter;

      if (:dirtbeforeanaliz is null) then dirtbeforeanaliz = :DirtAfterAnalise;
      if (:screeningbeforeanaliz is null) then screeningbeforeanaliz = :ScreeningfterAnalise;

/*  oкончательное определение DirtBefore  */
      if ((:dirtsum is not null) and (:dirtsum <> 0) and (:screeningsum is not null) and (:screeningsum <> 0)) then begin
        DirtBefore = :DirtBeforeAnaliz / :dirtsum * :dirt;
        ScreeningBefore = :screeningbeforeanaliz / :screeningsum * :screening;
/*  запись SamplerQualityID */

        select RESULTQUALITYID
        from quality_insert(null, :cropid, :classid, :sortid, null, null, :HumidityBefore, null, null, null, :GrainUnitBefore, null, null, :DirtBefore, :ScreeningBefore, null, :SmallGrainBefore, null, null, null)
        into :SamplerQualityID;

--  определение DirtAfter
        if ((:dirtbeforeanaliz is not null) and (:dirtbeforeanaliz <> 0) and (:screeningbeforeanaliz is not null) and (:screeningbeforeanaliz <> 0)) then begin
          dirtafter = :dirtbefore / :dirtbeforeanaliz * :dirtafteranalise;
          ScreeningAfter = :ScreeningBefore / :screeningbeforeanaliz * :ScreeningfterAnalise;
/*  запись LabQualityID */
        select RESULTQUALITYID
        from quality_insert(null, :cropid, :classid, :sortid, null, :analisysnumber, :HumidityAfter, null, null, null, :GrainUnitAfter, null, null, :dirtafter, :screeningafter, null, :SmallGrainAfter, null, null, null)
        into :LabQualityID;
/*  запись IT  */
          Update IT set LabQualityID = :LabQualityID, SamplerQualityID = :SamplerQualityID where InventoryID = :InventoryID;
        end
      end
    end
  end
END