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

STORAGETICKETS_QUALITY_CALC

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

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

Описание

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

Определение

CREATE PROCEDURE STORAGETICKETS_QUALITY_CALC(
    INVOICEREGISTERID INTEGER)
RETURNS (
    CROPID INTEGER,
    CLASSID INTEGER,
    SORTID INTEGER,
    TYPEID INTEGER,
    SUBTYPEID INTEGER,
    ANALYSISDATE DATE,
    SAMPLINGDATE DATE,
    USERID INTEGER,
    CREATETIME TIMESTAMP,
    QUALITYNUMBER INTEGER,
    HUMIDITY NUMERIC(9,2),
    GLUTEN NUMERIC(9,2),
    GLUTENQUALITY INTEGER,
    HARVESTYEAR INTEGER,
    GRAINUNIT INTEGER,
    ACIDITY NUMERIC(9,2),
    STARCH NUMERIC(9,2),
    PROTEIN NUMERIC(9,2),
    ASH NUMERIC(9,2),
    GLASSNESS INTEGER,
    DIRT NUMERIC(9,2),
    SCREENING NUMERIC(9,2),
    POISON NUMERIC(9,2),
    SMALLGRAIN NUMERIC(9,2),
    TURTLEBUG NUMERIC(9,2),
    REGIONID INTEGER,
    INFECTIONID INTEGER,
    PASS NUMERIC(9,2),
    PASS43 NUMERIC(9,2),
    PASS23 NUMERIC(9,2),
    PASS38 NUMERIC(9,2),
    REST NUMERIC(9,2),
    REST45 NUMERIC(9,2),
    REST43 NUMERIC(9,2),
    REST35 NUMERIC(9,2),
    REST27 NUMERIC(9,2),
    VOLUMERECOVERY INTEGER,
    RELATION_H_D NUMERIC(9,2),
    METALMAGNITID INTEGER,
    COLORID INTEGER,
    TASTEID INTEGER,
    SMELLID INTEGER,
    CRUNCHID INTEGER,
    SPOILTNUCLEIID INTEGER,
    SAFEGRAINID INTEGER,
    POTATOESSTICKID INTEGER,
    CLEANNESS NUMERIC(9,2),
    MICROORGANISM NUMERIC(9,2),
    KOLIFORM NUMERIC(9,2),
    MUSHROOMS NUMERIC(9,2),
    WHITENESS NUMERIC(9,2),
    MINERAL NUMERIC(9,2),
    DIRTY NUMERIC(9,2),
    BLUEGUZA NUMERIC(9,2),
    SAMPLEWEIGHT INTEGER,
    ACROSPIRE NUMERIC(9,2),
    DROPNUMBER INTEGER,
    INFRALUMGLUTEN INTEGER,
    GRAINROSECOLOR NUMERIC(9,2),
    GRAINSTATE INTEGER,
    PARTYSIMILARITYID INTEGER,
    PARTYINFECTIONID INTEGER,
    TRANSPORTSTATEID INTEGER,
    SESSION INTEGER,
    SEEIVEID INTEGER,
    SAMPLERID INTEGER,
    INFECTION_COUNT INTEGER,
    INFECTION_ACCUMULATION INTEGER,
    SPOILT NUMERIC(9,2),
    DEFECTIVE NUMERIC(9,2),
    GLASSNESSFULL INTEGER,
    STATEHUMIDITY INTEGER,
    STATEDIRT INTEGER,
    DIRTSEED NUMERIC(9,2),
    CROPSEED NUMERIC(9,2),
    FUSARIOSE NUMERIC(9,2),
    BRAND NUMERIC(9,2),
    WHEAT NUMERIC(9,2),
    RYE NUMERIC(9,2),
    BARLEY NUMERIC(9,2),
    GLUTENGROUP INTEGER,
    QUALITYSERTIFICATE INTEGER,
    PUNY NUMERIC(9,2),
    BEATEN NUMERIC(9,2),
    OILSEED NUMERIC(9,2),
    QUALITYHUMIDITYID INTEGER,
    OWNERID INTEGER)
AS
DECLARE VARIABLE QTYPE INTEGER;
DECLARE VARIABLE QVALUE DOUBLE PRECISION;
DECLARE VARIABLE QMAXVALUE NUMERIC(9,2);
DECLARE VARIABLE CATEGORYID INTEGER;
DECLARE VARIABLE USESUBTYPE INTEGER;
begin
  for
    select max(QUALITY.cropid), max(QUALITY.classid), max(QUALITY.sortid), QualityData.Qualitytypeid,
      max(qualitytypes.usesubtypes), max(qualitytypes.categoryid),
      sum(cast(QualityData.QualityValue as numeric(9, 2)) * cast(IT.NET as numeric(9, 2))) / nullif(sum(IT.NET + QualityData.QualityValue - QualityData.QualityValue), 0),
      max(QualityData.QualityValue), max(it.ownerid), max(invoiceregisters.registernumber), max(storagetickets.regdate)
    from invoiceregisters
      left join it on invoiceregisters.invoiceregisterid = it.registerid
      left join qualitydata on qualitydata.qualityid = it.labqualityid
      left join qualitytypes on (qualitytypes.qualitytypeid = QualityData.qualitytypeid)
      left join QUALITY on (IT.LABQUALITYID = QUALITY.QUALITYID)
      left join storagetickets on (storagetickets.storageticketid = invoiceregisters.storageticketid)
    where invoiceregisters.invoiceregisterid = :invoiceregisterid and it.state > -1
      and QualityData.Qualitytypeid not in (1, 54, 96, 86)
    group by QualityData.Qualitytypeid
    into :CROPID, :CLASSID, :SORTID, :QTYPE, :USESUBTYPE, :CATEGORYID, :QVALUE, :QMAXVALUE, :ownerid, :qualitynumber, :analysisdate
  do begin
    if (:USESUBTYPE = 1 or :CATEGORYID = 1) then begin
      QMAXVALUE = cast(QMAXVALUE as integer);
      if (:qtype = 6) then HARVESTYEAR = :QMAXVALUE;
      else if (:qtype = 18) then REGIONID = :QMAXVALUE;
      else if (:qtype = 19) then INFECTIONID = :QMAXVALUE;
      else if (:qtype = 24) then METALMAGNITID = :QMAXVALUE;
      else if (:qtype = 25) then COLORID = :QMAXVALUE;
      else if (:qtype = 26) then TASTEID = :QMAXVALUE;
      else if (:qtype = 27) then SMELLID = :QMAXVALUE;
      else if (:qtype = 28) then CRUNCHID = :QMAXVALUE;
      else if (:qtype = 29) then SPOILTNUCLEIID = :QMAXVALUE;
      else if (:qtype = 30) then SAFEGRAINID = :QMAXVALUE;
      else if (:qtype = 34) then POTATOESSTICKID = :QMAXVALUE;
      else if (:qtype = 54) then sampleweight = :QMAXVALUE;
      else if (:qtype = 87) then INFRALUMGLUTEN = :QMAXVALUE;
      else if (:qtype = 88) then SEEIVEID = :QMAXVALUE;
      else if (:qtype = 92) then PARTYSIMILARITYID = :QMAXVALUE;
      else if (:qtype = 94) then PARTYINFECTIONID = :QMAXVALUE;
      else if (:qtype = 95) then TRANSPORTSTATEID = :QMAXVALUE;
      else if (:qtype = 101) then INFECTION_ACCUMULATION = :QMAXVALUE;
      else if (:QTYPE = 102) then STATEHUMIDITY = :QMAXVALUE;
      else if (:QTYPE = 103) then STATEDIRT = :QMAXVALUE;
      else if (:qtype = 111) then typeid = :QMAXVALUE;
      else if (:qtype = 112) then subtypeid = :QMAXVALUE;
      else if (:qtype = 5) then GLUTENGROUP = :QMAXVALUE;
      else if (:qtype = 86) then SAMPLERID = :QMAXVALUE;
      else if (:qtype = 89) then GRAINSTATE = :QMAXVALUE;
    end
    else begin
      if (:qtype = 3) then GLUTEN = cast(:qvalue as numeric(9,1));
      else if (:qtype = 4) then GLUTENQUALITY = cast(:qvalue as integer);
      else if (:qtype = 7) then GRAINUNIT = cast(:qvalue as integer);
      else if (:qtype = 12) then glassness = cast(:qvalue as integer);
      else if (:qtype = 22) then VOLUMERECOVERY = cast(:qvalue as integer);
      else begin
        qvalue = cast(:qvalue as numeric(9, 2));
        if (:qtype = 2) then HUMIDITY = :qvalue;
        else if (:qtype = 8) then ACIDITY = :qvalue;
        else if (:qtype = 9) then STARCH = :qvalue;
        else if (:qtype = 10) then PROTEIN = :qvalue;
        else if (:qtype = 11) then ASH = :qvalue;
        else if (:qtype = 13) then DIRT = :qvalue;
        else if (:qtype = 14) then SCREENING = :qvalue;
        else if (:qtype = 15) then POISON = :qvalue;
        else if (:qtype = 16) then SMALLGRAIN = :qvalue;
        else if (:qtype = 17) then TURTLEBUG = :qvalue;
        else if (:qtype = 23) then RELATION_H_D = :qvalue;
        else if (:qtype = 39) then CLEANNESS = :qvalue;
        else if (:qtype = 40) then MICROORGANISM = :qvalue;
        else if (:qtype = 41) then KOLIFORM = :qvalue;
        else if (:qtype = 42) then MUSHROOMS = :qvalue;
        else if (:qtype = 43) then WHITENESS = :qvalue;
        else if (:qtype = 46) then MINERAL = :qvalue;
        else if (:qtype = 52) then DIRTY = :qvalue;
        else if (:qtype = 53) then BLUEGUZA = :qvalue;

        else if (:qtype = 56) then ACROSPIRE = :qvalue;
        else if (:qtype = 59) then DROPNUMBER = :qvalue;
        else if (:qtype = 62) then DIRTSEED = :qvalue;
        else if (:qtype = 66) then BEATEN = :qvalue;
        else if (:qtype = 68) then PUNY = :qvalue;
        else if (:qtype = 70) then Defective = :qvalue;
        else if (:qtype = 71) then wheat = :qvalue;
        else if (:qtype = 73) then barley = :qvalue;
        else if (:qtype = 74) then rye = :qvalue;
        else if (:qtype = 79) then spoilt = :qvalue;
        else if (:qtype = 80) then FUSARIOSE = :qvalue;
        else if (:qtype = 90) then GRAINROSECOLOR = :qvalue;
        else if (:qtype = 99) then INFECTION_COUNT = :qvalue;
        else if (:qtype = 107) then GLASSNESSFULL = :qvalue;
        else if (:qtype = 108) then cropseed = :qvalue;
        else if (:qtype = 109) then Brand = :qvalue;
        else if (:qtype = 110) then OilSeed = :qvalue;
        else if (:qtype = 35) then begin
          PASS43 = :qvalue;
          PASS = :qvalue;
        end
        else if (:qtype = 21) then begin
          PASS23 = :qvalue;
          PASS = :qvalue;
        end
        else if (:qtype = 36) then begin
          PASS38 = :qvalue; /* or45 */
          PASS = :qvalue;
        end
        else if (:qtype = 44) then begin
          REST45 = :qvalue;
          REST = :qvalue;
        end
        else if (:qtype = 20) then begin
          REST43 = :qvalue;
          REST = :qvalue;
        end
        else if (:qtype = 37) then begin
          REST35 = :qvalue;
          REST = :qvalue;
        end
        else if (:qtype = 38) then begin
          REST27 = :qvalue;
          REST = :qvalue;
        end
      end
    end
  end

  SUSPEND;
/*    ORGANIC NUMERIC(9,2),
    PASS1_5 NUMERIC(9,2),
    SPUR
    corncockle
    beaten
    corroded
    puny
    DEFECTIVE -- Поврежденных
    wheat -- Пшеница
*/
END