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

ACC_OUTCOMEGROUPS_UPDATE

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

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

(Нет выходных параметров)

Описание

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

Определение

CREATE PROCEDURE ACC_OUTCOMEGROUPS_UPDATE(
    REGDATE DATE,
    OWNERID INTEGER,
    CONTRACTID INTEGER,
    PROVIDERID INTEGER,
    STORAGEID INTEGER,
    OPERATIONID INTEGER,
    STORINGID INTEGER,
    PACKID INTEGER,
    PARTYID INTEGER,
    PARTYDATE DATE,
    CROPID INTEGER,
    CLASSID INTEGER,
    SORTID INTEGER,
    HUMIDITY NUMERIC(5,2),
    DIRT NUMERIC(5,2),
    QUALITYNUMBER INTEGER,
    TRANSPORTSUBTYPE INTEGER,
    ORDERLOADID INTEGER,
    NEWORDERLOADITEMID INTEGER,
    NEWOWNERID INTEGER,
    NEWPROVIDERID INTEGER,
    NEWCONTRACTID INTEGER,
    NEWSTORAGEID INTEGER,
    NEWSTORINGID INTEGER,
    NEWPACKID INTEGER,
    UPDATEQUALITY SMALLINT,
    NEWPARTYID INTEGER,
    NEWCROPID INTEGER,
    NEWCLASSID INTEGER,
    NEWSORTID INTEGER,
    NEWHUM NUMERIC(5,2),
    NEWDIRT NUMERIC(5,2),
    NEWQNUM INTEGER,
    NEWBUHQUALITYID INTEGER,
    NEWHARVESTYEAR INTEGER)
AS
declare variable INVENTORYID integer;
declare variable SAMPLERQUALITYID integer;
declare variable LABQUALITYID integer;
begin
  for
    select IT.INVENTORYID, IT.SAMPLERQUALITYID, coalesce(IT.LABQUALITYID,:NEWBUHQUALITYID)
      from IT
        join GETWORKPARTIES(:PARTYID, :PARTYDATE, null, null, null) on (GETWORKPARTIES.PARTYID = IT.PARTYID)
        left join INVOICES on (INVOICES.INVOICEID = IT.INVOICEID)
        left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
        left join ORDERLOADITEMS on (ORDERLOADITEMS.ORDERLOADITEMID = IT.ORDERLOADITEMID)
        left join ORDERLOAD on (ORDERLOAD.ORDERLOADID = ORDERLOADITEMS.ORDERLOADID)
        left join STATIONS on (STATIONS.STATIONID = INVOICES.STATIONID)
        left join OPERATION on (OPERATION.OPERATIONID = IT.OPERATIONID)
        left join QUALITY Q on (Q.QUALITYID = IT.SAMPLERQUALITYID)

        left join QUALITY_CROPFULLNAME(Q.CROPID, Q.CLASSID, null, 1) FULLNAME on (0=0)
        left join QUALITYDATA_SELECT_MIN(IT.BUHQUALITYID) QLAB on (0=0)
        left join CROPSORT on (CROPSORT.SORTID = Q.SORTID)

        left join CLIENTS OWNER on (OWNER.CLIENTID = IT.OWNERID)
        left join CONTRACTS on (CONTRACTS.CONTRACTID = IT.CONTRACTID)
        left join PACKS on (PACKS.PACKID = IT.PACKID)
        left join STORAGE on (STORAGE.STORAGEID = IT.STORAGEID)
        left join STORING on (STORING.STORINGID = IT.STORINGID)
        left join TRANSPORTTYPE on (TRANSPORTTYPE.TRANSPORTTYPEID = ITTRANSPORT.TRANSPORTTYPEID)
        left join CLIENTS PROVIDER on (PROVIDER.CLIENTID = IT.PROVIDERID)
      where (IT.REGDATE = :REGDATE) and (IT.PARENTID >= 0) and (IT.STATE >= 0)
        and (IT.OPERATIONID = :OPERATIONID) and (IT.STORAGEID = :STORAGEID)
        and (coalesce(IT.STORINGID,0) = :STORINGID)
        and (coalesce(IT.OWNERID,0) = :OWNERID)
        and (coalesce(IT.CONTRACTID,0) = :CONTRACTID)
        and (coalesce(IT.PROVIDERID,0) = :PROVIDERID)
        and (coalesce(IT.PACKID,0) = :PACKID)
        and (Q.CROPID = :CROPID) and (coalesce(Q.CLASSID,0) = :CLASSID)
        and (coalesce(Q.SORTID,0) = :SORTID)
        and (coalesce(QLAB.HUMIDITY,0) = :HUMIDITY)
        and (coalesce(QLAB.DIRT,0) = :DIRT)
        and (coalesce(QLAB.QUALITYNUMBER,0) = :QUALITYNUMBER)
        and (coalesce(TRANSPORTTYPE.TRANSPORTSUBTYPE,0) = :TRANSPORTSUBTYPE)
        and (coalesce(ORDERLOAD.ORDERLOADID,0) = :ORDERLOADID)

--   and cs.STORAGEID is null
    into :INVENTORYID, :SAMPLERQUALITYID, :LABQUALITYID
  do begin
    update IT
      set PARTYID = :NEWPARTYID,
          ORDERLOADITEMID = :NEWORDERLOADITEMID,
          OWNERID = :NEWOWNERID,
          PROVIDERID = :NEWPROVIDERID,
          CONTRACTID = :NEWCONTRACTID,
          STORAGEID = :NEWSTORAGEID,
          STORINGID = :NEWSTORINGID,
          PACKID = :NEWPACKID,
          LABQUALITYID = :LABQUALITYID
      where (INVENTORYID = :INVENTORYID);

    if (:UPDATEQUALITY = 1) then begin
      update QUALITY
        set CROPID = :NEWCROPID,
            CLASSID = :NEWCLASSID,
            SORTID = :NEWSORTID
        where (QUALITYID = :SAMPLERQUALITYID);

      if (:LABQUALITYID is not null) then begin
        update QUALITY
          set CROPID = :NEWCROPID,
              CLASSID = :NEWCLASSID,
              SORTID = :NEWSORTID
          where (QUALITYID = :LABQUALITYID);

        if (row_count = 0) then
          insert into QUALITY(QUALITYID, CROPID, CLASSID, SORTID)
            values (:LABQUALITYID, :NEWCROPID, :NEWCLASSID, :NEWSORTID);

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

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

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

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

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

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

        if (:NEWHARVESTYEAR is null) then
          delete from QUALITYDATA
            where ((QUALITYDATA.QUALITYID = :LABQUALITYID) and (QUALITYDATA.QUALITYTYPEID = 6));
        else begin
          update QUALITYDATA
            set QUALITYVALUE = :NEWHARVESTYEAR
            where ((QUALITYID = :LABQUALITYID) and (QUALITYTYPEID = 6));

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