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

FORM117_CALC_MIDI_HUMIDITY

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

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

Описание

Расчет формы 117 - определение влажности

Определение

CREATE PROCEDURE FORM117_CALC_MIDI_HUMIDITY(
    BEGDATE DATE,
    ENDDATE DATE,
    STORAGEID INTEGER)
RETURNS (
    HUMIDITY DOUBLE PRECISION,
    WEIGHT INTEGER)
AS
begin
    for
      select
        cast(sum(quality_select.humidity * it.net) / sum(nullif(it.net, 0) + quality_select.humidity - quality_select.humidity) as numeric(9,2)),
        cast(sum(nullif(it.net, 0) + quality_select.humidity - quality_select.humidity) as integer)
      from it
        left join quality_select(it.labqualityid) on (0 = 0)
        left join ittransport on it.inventoryid = ittransport.inventoryid
      where it.operationid = 5
        and ittransport.sampleroutdate between :begdate and :enddate
        and it.storageid = :storageid
        and it.state > -1
        and it.parentid <= 0
        and it.labqualityid is not null
        and it.weight is not null
        and quality_select.classid in (select classid from storingrelation where storingrelation.storageid = :storageid and storingrelation.operationparam = 2 and storingrelation.storingid = 6)

      union

      select
        cast(sum(qualitydata.qualityvalue * gradeouts.weight) / sum(nullif(gradeouts.weight, 0) + qualitydata.qualityvalue - qualitydata.qualityvalue)  as numeric(9,2)),
        cast(sum(nullif(gradeouts.weight, 0) + qualitydata.qualityvalue - qualitydata.qualityvalue) as integer)
      from graderesults
        left join gradeouts on (graderesults.graderesultid = gradeouts.graderesultid)
        left join qualitydata on (gradeouts.qualityid = qualitydata.qualityid)
      where
        ((graderesults.workingdate between :begdate and :enddate
          and graderesults.storingid in (3, 4, 5)
         )
         or graderesults.graderesultid < -1
        )
        and graderesults.storageid = :StorageID
        and qualitydata.qualitytypeid = 2
        and gradeouts.classid in (select classid from storingrelation where storingrelation.storageid = :storageid and storingrelation.operationparam = 2 and storingrelation.storingid = graderesults.storingid)
      group by qualitydata.qualitytypeid

      into :humidity, :weight
    do suspend;
END