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

IT_COPYSTORAGEMOVE

Описание

(Нет описания для триггера IT_COPYSTORAGEMOVE)

Определение

CREATE TRIGGER IT_COPYSTORAGEMOVE FOR IT
ACTIVE AFTER INSERT OR UPDATE POSITION 0
as
declare variable TARGETSTORAGEID integer;
declare variable TARGETSTORAGECLIENTID integer;
declare variable NEWINVENTORYID integer;
declare variable NEWINVOICEID integer;
declare variable OLDINVOICEID integer;
declare variable NEWTRANSPORTTYPEID integer;

begin
  if ((new.STORAGEID is not null) and (new.OPERATIONID in (1,5)) and (new.STATE <> 200)) then begin
--Переделать чтобы было по настройкам
/*
    select CLIENTS.STORAGEID, INVOICES.REGISTERIDOLD
      from INVOICES
        left join CLIENTS on (CLIENTS.CLIENTID = INVOICES.SENDERID)
      where (INVOICES.INVOICEID = new.INVOICEID)
    into :TARGETSTORAGEID, :TARGETSTORAGECLIENTID;
*/
    select INVOICES.REGISTERIDOLD
      from INVOICES
      where (INVOICES.INVOICEID = new.INVOICEID)
    into :TARGETSTORAGEID;
--    Select StorageID from clients where clients.ClientID = new.OwnerID -- для николаева owner и Provider наоборот new.ProviderID
--    into :ProviderStorage;

--    if ((TARGETSTORAGEID is not null) and ((new.ROUTEID is not null) and (new.ROUTEID > 0))) then begin
    if (coalesce(TARGETSTORAGEID,0) > 0) then begin
      NEWINVENTORYID = null;

      select INVENTORYID
        from IT
        where (IT.PARENTID = new.INVENTORYID)
      into :NEWINVENTORYID;

      if (NEWINVENTORYID is null) then begin
--
        NEWINVENTORYID = gen_id(INVENTORYIDGEN, 1);

        insert into ITTRANSPORT(INVENTORYID, BARCODE, CAPACITYWEIGHT, CAPACITYVOLUME, SAMPLERINDATE,
            SAMPLERINTIME, SAMPLEROUTDATE, SAMPLEROUTTIME, TAREBYHAND, GROSSBYHAND, LOADTIME, TRANSPORTTYPEID,
            TRANSPORTNUMBER, CHECKPOINTINTIME, CHECKPOINTOUTTIME, WEIGHTSESSION, INVOICEGROSS, INVOICETARE,
            INVOICENET, SENDERQUALITYID, OWNERFIELDID, OWNERSTORAGEID, BUNKERS, GRAINFIELDOUTTIME,
            CHECKPOINTSESSION, SAMPLERSESSION)
          select :NEWINVENTORYID, BARCODE, CAPACITYWEIGHT, CAPACITYVOLUME, SAMPLERINDATE,
              SAMPLERINTIME, SAMPLEROUTDATE, SAMPLEROUTTIME, TAREBYHAND, GROSSBYHAND, LOADTIME, TRANSPORTTYPEID,
              TRANSPORTNUMBER, CHECKPOINTINTIME, CHECKPOINTOUTTIME, WEIGHTSESSION, INVOICEGROSS, INVOICETARE,
              INVOICENET, SENDERQUALITYID, OWNERFIELDID, OWNERSTORAGEID, BUNKERS, GRAINFIELDOUTTIME,
              CHECKPOINTSESSION, SAMPLERSESSION
            from ITTRANSPORT
            where (INVENTORYID = new.INVENTORYID);

        select ITTRANSPORT.TRANSPORTTYPEID
          from ITTRANSPORT
          where (ITTRANSPORT.INVENTORYID = new.INVENTORYID)
        into :NEWTRANSPORTTYPEID;

        if (NEWTRANSPORTTYPEID = 1) then begin
          NEWINVOICEID = null;

          select IT.INVOICEID
            from IT
            where (IT.PARENTID = (select IT1.INVENTORYID
                                    from IT IT1
                                      left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT1.INVENTORYID)
                                  where (IT1.INVOICEID = (select IT2.INVOICEID
                                                            from IT IT2
                                                            where (IT2.INVENTORYID = new.INVENTORYID)))
                                    and (ITTRANSPORT.TRANSPORTTYPEID = 0) and (IT1.STATE >= 0)))
          into :NEWINVOICEID;

          if (NEWINVOICEID is null) then
            NEWINVOICEID = gen_id(INVOICEIDGEN, 1);
        end
        else NEWINVOICEID = gen_id(INVOICEIDGEN, 1);

        OLDINVOICEID = null;
        select INVOICES.INVOICEID
          from INVOICES
          where (INVOICES.INVOICEID = :NEWINVOICEID)
        into :OLDINVOICEID;

        if (OLDINVOICEID is null) then
          insert into INVOICES(INVOICEID, INVOICENUMBER, INVOICEDATE, STATIONID, SENDERID, DRIVERS)
            select :NEWINVOICEID, INVOICENUMBER, INVOICEDATE, STATIONID, :TARGETSTORAGECLIENTID, DRIVERS
              from INVOICES
              where (INVOICEID = new.INVOICEID);

        insert into IT(INVENTORYID, PARENTID, PARTYID, REGDATE, OPERATIONID, INVOICEID, ORDERLOADITEMID,
            GROSS, TARE, NET, WEIGHT, PACKID, CARGOQUANTITY, TARETIME, GROSSTIME, OWNERID, PROVIDERID,
            RELATIONID, SAMPLERQUALITYID, LABQUALITYID, SILAGEID, SILAGEUSERID, ROUTEID, STORAGEID,
            STORINGID, CONTRACTID, UWORDERID, WEIGHTNUMBER, WEIGHTUSERID, LOADCONFIRMTIME, STATE)
          values (
            :NEWINVENTORYID,
            new.INVENTORYID, --:PARENTID
            new.PARTYID, --:PARTYID
            new.REGDATE, --:REGDATE
            case new.OPERATIONID
              when 1 then
                5
              when 5 then
                1
            end, --:OPERATIONID
--            null, --:OPERATIONPARAM
            :NEWINVOICEID, --:INVOICEID
            new.ORDERLOADITEMID, --:ORDERLOADITEMID
            new.GROSS, --:GROSS
            new.TARE, --:TARE
            new.NET, --:NET
            new.WEIGHT, --:WEIGHT
            new.PACKID, --:PACKID
            new.CARGOQUANTITY, --:CARGOQUANTITY
            new.TARETIME, --:TARETIME
            new.GROSSTIME, --:GROSSTIME
            new.PROVIDERID, --:OWNERID
            new.OWNERID, --:PROVIDERID
            new.RELATIONID, --:RELATIONID
            new.SAMPLERQUALITYID, --:SAMPLERQUALITYID
            new.LABQUALITYID, --:LABQUALITYID
            null, --:SILAGEID
            null, --:SILAGEUSERID
            null, --:ROUTEID
            :TARGETSTORAGEID, --:STORAGEID
            null, --:STORINGID
            null, --:CONTRACTID
            null, --:UWORDERID
            null, --:WEIGHTNUMBER
            null, --:WEIGHTUSERID
            new.LOADCONFIRMTIME, --:LOADCONFIRMTIME
            200); --:STATE
      end
      else begin
        update IT
        set REGDATE = new.REGDATE,
            OPERATIONID = case new.OPERATIONID
                            when 1 then
                              5
                            when 5 then
                              1
                          end,
--            OPERATIONPARAM = new.OPERATIONPARAM,
--            INVOICEID = new.INVOICEID,
            ORDERLOADITEMID = new.ORDERLOADITEMID,
            GROSS = new.GROSS,
            TARE = new.TARE,
            NET = new.NET,
            WEIGHT = new.WEIGHT,
            PACKID = new.PACKID,
            CARGOQUANTITY = new.CARGOQUANTITY,
            TARETIME = new.TARETIME,
            GROSSTIME = new.GROSSTIME,
            OWNERID = new.PROVIDERID,
            PROVIDERID = new.OWNERID,
            RELATIONID = new.RELATIONID,
            SAMPLERQUALITYID = new.SAMPLERQUALITYID,
            LABQUALITYID = new.LABQUALITYID,
            SILAGEID = new.SILAGEID,
            SILAGEUSERID = new.SILAGEUSERID,
            ROUTEID = new.ROUTEID,
            STORAGEID = :TARGETSTORAGEID,
            STORINGID = new.STORINGID,
--            WEIGHTNUMBER = new.WEIGHTNUMBER,
            WEIGHTUSERID = new.WEIGHTUSERID,
            LOADCONFIRMTIME = new.LOADCONFIRMTIME,
            STATE = case new.STATE
                      when -1 then
                        -1
                       else 200
                     end,
            COMMENT = new.COMMENT
        where (INVENTORYID = :NEWINVENTORYID);
      end
    end
  end
end