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

QUALITY_UPDATE

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

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

Описание

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

Определение

CREATE PROCEDURE QUALITY_UPDATE(
    USECOPYING INTEGER,
    QUALITYID INTEGER,
    CROPID INTEGER,
    CLASSID INTEGER,
    SORTID INTEGER,
    ANALYSISDATE DATE,
    QUALITYNUMBER INTEGER,
    HUMIDITY NUMERIC(9,2),
    GLUTEN NUMERIC(9,1),
    GLUTENQUALITY INTEGER,
    HARVESTYEAR INTEGER,
    GRAINUNIT INTEGER,
    ASH NUMERIC(9,2),
    GLASSNESS INTEGER,
    DIRT NUMERIC(9,2),
    SCREENING NUMERIC(9,2),
    POISON NUMERIC(9,2),
    SMALLGRAIN NUMERIC(9,2),
    REGIONID INTEGER,
    DROPNUMBER INTEGER)
RETURNS (
    RESULTQUALITYID INTEGER)
AS
begin
/*
   Не ИСПОЛЬЗОВАТЬ без разрешения
   Все права защищены
*/
  if (:USECOPYING = 1) then
    execute procedure QUALITY_COPY(:QUALITYID) returning_values :RESULTQUALITYID;
  else RESULTQUALITYID = :QUALITYID;

  update QUALITY
  set CROPID = :CROPID,
      CLASSID = :CLASSID,
      SORTID = :SORTID,
      ANALYSISDATE = :ANALYSISDATE
  where (QUALITYID = :RESULTQUALITYID);


  if ((row_count = 0) and (:USECOPYING < 2)) then
    insert into QUALITY (QUALITYID, CROPID, CLASSID, SORTID, ANALYSISDATE)
      values (:RESULTQUALITYID, :CROPID, :CLASSID, :SORTID, :ANALYSISDATE);


  if (:QUALITYNUMBER is null) then
    delete from QUALITYDATA
    where (QUALITYID = :RESULTQUALITYID) and (QUALITYTYPEID = 1);
  else begin
    update QUALITYDATA
    set QUALITYVALUE = :QUALITYNUMBER
    where (QUALITYID = :RESULTQUALITYID) and (QUALITYTYPEID = 1);

    if (row_count = 0) then
      insert into QUALITYDATA (QUALITYID, QUALITYTYPEID, QUALITYVALUE)
        values (:RESULTQUALITYID, 1, :QUALITYNUMBER);
  end

  if (:HUMIDITY is null) then
    delete from QUALITYDATA
    where (QUALITYID = :RESULTQUALITYID) and (QUALITYTYPEID = 2);
  else begin
    update QUALITYDATA
    set QUALITYVALUE = :HUMIDITY
    where (QUALITYID = :RESULTQUALITYID) and (QUALITYTYPEID = 2);

    if (row_count = 0) then
      insert into QUALITYDATA (QUALITYID, QUALITYTYPEID, QUALITYVALUE)
        values (:RESULTQUALITYID, 2, :HUMIDITY);
  end

  if (:GLUTEN is null) then
    delete from QUALITYDATA
    where (QUALITYID = :RESULTQUALITYID) and (QUALITYTYPEID = 3);
  else begin
    update QUALITYDATA
    set QUALITYVALUE = :GLUTEN
    where (QUALITYID = :RESULTQUALITYID) and (QUALITYTYPEID = 3);

    if (row_count = 0) then
      insert into QUALITYDATA (QUALITYID, QUALITYTYPEID, QUALITYVALUE)
        values (:RESULTQUALITYID, 3, :GLUTEN);
  end
--
  if (:GLUTENQUALITY is null) then
    delete from QUALITYDATA
    where (QUALITYID = :RESULTQUALITYID) and (QUALITYTYPEID = 4);
  else begin
    update QUALITYDATA
    set QUALITYVALUE = :GLUTENQUALITY
    where (QUALITYID = :RESULTQUALITYID) and (QUALITYTYPEID = 4);

    if (row_count = 0) then
      insert into QUALITYDATA (QUALITYID, QUALITYTYPEID, QUALITYVALUE)
        values (:RESULTQUALITYID, 4, :GLUTENQUALITY);
  end
--
  if (:HARVESTYEAR is null) then
    delete from QUALITYDATA
    where (QUALITYID = :RESULTQUALITYID) and (QUALITYTYPEID = 6);
  else begin
    update QUALITYDATA
    set QUALITYVALUE = :HARVESTYEAR
    where (QUALITYID = :RESULTQUALITYID) and (QUALITYTYPEID = 6);

    if (row_count = 0) then
      insert into QUALITYDATA (QUALITYID, QUALITYTYPEID, QUALITYVALUE)
        values (:RESULTQUALITYID, 6, :HARVESTYEAR);
  end

  if (:GRAINUNIT is null) then
    delete from QUALITYDATA
    where (QUALITYID = :RESULTQUALITYID) and (QUALITYTYPEID = 7);
  else begin
    update QUALITYDATA
    set QUALITYVALUE = :GRAINUNIT
    where (QUALITYID = :RESULTQUALITYID) and (QUALITYTYPEID = 7);

    if (row_count = 0) then
      insert into QUALITYDATA (QUALITYID, QUALITYTYPEID, QUALITYVALUE)
        values (:RESULTQUALITYID, 7, :GRAINUNIT);
  end

  if (:ASH is null) then
    delete from QUALITYDATA
    where (QUALITYID = :RESULTQUALITYID) and (QUALITYTYPEID = 11);
  else begin
    update QUALITYDATA
    set QUALITYVALUE = :ASH
    where (QUALITYID = :RESULTQUALITYID) and (QUALITYTYPEID = 11);

    if (row_count = 0) then
      insert into QUALITYDATA (QUALITYID, QUALITYTYPEID, QUALITYVALUE)
        values (:RESULTQUALITYID, 11, :ASH);
  end

  if (:GLASSNESS is null) then
    delete from QUALITYDATA
      where (QUALITYID = :RESULTQUALITYID) and (QUALITYTYPEID = 12);
  else begin
    update QUALITYDATA
    set QUALITYVALUE = :GLASSNESS
    where (QUALITYID = :RESULTQUALITYID) and (QUALITYTYPEID = 12);

    if (row_count = 0) then
      insert into QUALITYDATA (QUALITYID, QUALITYTYPEID, QUALITYVALUE)
        values (:RESULTQUALITYID, 12, :GLASSNESS);
  end

  if (:DIRT is null) then
    delete from QUALITYDATA
      where (QUALITYID = :RESULTQUALITYID) and (QUALITYTYPEID = 13);
  else begin
    update QUALITYDATA
    set QUALITYVALUE = :DIRT
    where (QUALITYID = :RESULTQUALITYID) and (QUALITYTYPEID = 13);

    if (row_count = 0) then
      insert into QUALITYDATA (QUALITYID, QUALITYTYPEID, QUALITYVALUE)
        values (:RESULTQUALITYID, 13, :DIRT);
  end

  if (:SCREENING is null) then
    delete from QUALITYDATA
      where (QUALITYID = :RESULTQUALITYID) and (QUALITYTYPEID = 14);
  else begin
    update QUALITYDATA
    set QUALITYVALUE = :SCREENING
    where (QUALITYID = :RESULTQUALITYID) and (QUALITYTYPEID = 14);

    if (row_count = 0) then
      insert into QUALITYDATA (QUALITYID, QUALITYTYPEID, QUALITYVALUE)
        values (:RESULTQUALITYID, 14, :SCREENING);
  end

  if (:DROPNUMBER is null) then
    delete from QUALITYDATA
      where (QUALITYID = :RESULTQUALITYID) and (QUALITYTYPEID = 59);
  else begin
    update QUALITYDATA
    set QUALITYVALUE = :DROPNUMBER
    where (QUALITYID = :RESULTQUALITYID) and (QUALITYTYPEID = 59);

    if (row_count = 0) then
      insert into QUALITYDATA (QUALITYID, QUALITYTYPEID, QUALITYVALUE)
        values (:RESULTQUALITYID, 59, :DROPNUMBER);
  end

  if (:POISON is null) then
    delete from QUALITYDATA
      where (QUALITYID = :RESULTQUALITYID) and (QUALITYTYPEID = 15);
  else begin
    update QUALITYDATA
    set QUALITYVALUE = :POISON
    where (QUALITYID = :RESULTQUALITYID) and (QUALITYTYPEID = 15);

    if (row_count = 0) then
      insert into QUALITYDATA (QUALITYID, QUALITYTYPEID, QUALITYVALUE)
        values (:RESULTQUALITYID, 15, :POISON);
  end

  if (:SMALLGRAIN is null) then
    delete from QUALITYDATA
      where (QUALITYID = :RESULTQUALITYID) and (QUALITYTYPEID = 16);
  else begin
    update QUALITYDATA
    set QUALITYVALUE = :SMALLGRAIN
    where (QUALITYID = :RESULTQUALITYID) and (QUALITYTYPEID = 16);

    if (row_count = 0) then
      insert into QUALITYDATA (QUALITYID, QUALITYTYPEID, QUALITYVALUE)
        values (:RESULTQUALITYID, 16, :SMALLGRAIN);
  end

  if (:REGIONID is null) then
    delete from QUALITYDATA
      where (QUALITYID = :RESULTQUALITYID) and (QUALITYTYPEID = 18);
  else begin
    update QUALITYDATA
    set QUALITYVALUE = :REGIONID
    where (QUALITYID = :RESULTQUALITYID) and (QUALITYTYPEID = 18);

    if (row_count = 0) then
      insert into QUALITYDATA (QUALITYID, QUALITYTYPEID, QUALITYVALUE)
        values (:RESULTQUALITYID, 18, :REGIONID);
  end

  suspend;
end