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

IT_HISTORY_RECORD

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

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

Описание

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

Определение

CREATE PROCEDURE IT_HISTORY_RECORD(
    INVENTORYID INTEGER,
    ONLY_CHANGE_DATA INTEGER)
RETURNS (
    PARTYID INTEGER,
    REGDATE DATE,
    OPERATIONID INTEGER,
    GROSS INTEGER,
    TARE INTEGER,
    NET INTEGER,
    SAMPLERINDATE DATE,
    PROVIDERID INTEGER,
    SAMPLERQUALITYID INTEGER,
    LABQUALITYID INTEGER,
    SILAGEID INTEGER,
    ROUTEID INTEGER,
    STORAGEID INTEGER,
    CONTRACTID INTEGER,
    STATE INTEGER,
    CREATETIME TIMESTAMP,
    USERNAME VARCHAR(50),
    HISTORY_OPERATION INTEGER)
AS
DECLARE VARIABLE ACREATETIME TIMESTAMP;
DECLARE VARIABLE AUSERNAME VARCHAR(50);
DECLARE VARIABLE AHISTORY_OPERATION INTEGER;
DECLARE VARIABLE FIELDNAME CHAR(31);
DECLARE VARIABLE TABLEID INTEGER;
DECLARE VARIABLE PARTYIDID INTEGER;
DECLARE VARIABLE REGDATEID INTEGER;
DECLARE VARIABLE OPERATIONIDID INTEGER;
DECLARE VARIABLE GROSSID INTEGER;
DECLARE VARIABLE TAREID INTEGER;
DECLARE VARIABLE NETID INTEGER;
DECLARE VARIABLE SAMPLERINDATEID INTEGER;
DECLARE VARIABLE PROVIDERIDID INTEGER;
DECLARE VARIABLE SAMPLERQUALITYIDID INTEGER;
DECLARE VARIABLE LABQUALITYIDID INTEGER;
DECLARE VARIABLE SILAGEIDID INTEGER;
DECLARE VARIABLE ROUTEIDID INTEGER;
DECLARE VARIABLE STORAGEIDID INTEGER;
DECLARE VARIABLE CONTRACTIDID INTEGER;
DECLARE VARIABLE STATEID INTEGER;
DECLARE VARIABLE TABLEOPERATIONID INTEGER;
DECLARE VARIABLE ATABLEOPERATIONID INTEGER;
DECLARE VARIABLE FIELDID INTEGER;
DECLARE VARIABLE FIELDVALUE VARCHAR(255);
begin
  select rdb$relation_id
  from rdb$relations
  where rdb$relation_name = 'IT'
  into :TableID;

  for
    select rdb$field_id, rdb$field_name
    from rdb$relation_fields
    where rdb$relation_name = 'IT'
    into :fieldid, :FieldName
  do begin
     if (:fieldname = 'PARTYID') then PARTYIDid = :fieldid;
    else if (:fieldname = 'REGDATE') then REGDATEid = :fieldid;
    else if (:fieldname = 'OPERATIONID') then OPERATIONIDid = :fieldid;
    else if (:fieldname = 'GROSS') then GROSSid = :fieldid;
    else if (:fieldname = 'TARE') then TAREid = :fieldid;
    else if (:fieldname = 'NET') then NETid = :fieldid;
    else if (:fieldname = 'PROVIDERID') then PROVIDERIDid = :fieldid;
    else if (:fieldname = 'SAMPLERQUALITYID') then SAMPLERQUALITYIDid = :fieldid;
    else if (:fieldname = 'LABQUALITYID') then LABQUALITYIDid = :fieldid;
    else if (:fieldname = 'SILAGEID') then SILAGEIDid = :fieldid;
    else if (:fieldname = 'ROUTEID') then ROUTEIDid = :fieldid;
    else if (:fieldname = 'STORAGEID') then STORAGEIDid = :fieldid;
    else if (:fieldname = 'CONTRACTID') then CONTRACTIDid = :fieldid;
    else if (:fieldname = 'STATE') then STATEid = :fieldid;
  end

  TABLEOPERATIONID = null;
  for
    select ilt$tableoperation.tableoperationid, ilt$fieldinteger.fieldid, cast(ilt$fieldinteger.fieldvalue as varchar(255)),
      ilt$tableoperation.createtime, users_fullname.userfullname, ilt$tableoperation.operationid
    from ilt$tableoperation
      left join ilt$fieldinteger on (ilt$tableoperation.tableoperationid = ilt$fieldinteger.tableoperationid)
      left join users_fullname(ilt$tableoperation.userid) on (0 = 0)
    where ilt$tableoperation.tableid = :TableID
      and ilt$tableoperation.recordid = :InventoryID
      and ilt$fieldinteger.fieldid is not null
    union
    select ilt$tableoperation.tableoperationid, ilt$fielddate.fieldid, cast(ilt$fielddate.fieldvalue as varchar(255)),
      ilt$tableoperation.createtime, users_fullname.userfullname, ilt$tableoperation.operationid
    from ilt$tableoperation
      left join ilt$fielddate on (ilt$tableoperation.tableoperationid = ilt$fielddate.tableoperationid)
      left join users_fullname(ilt$tableoperation.userid) on (0 = 0)
    where ilt$tableoperation.tableid = :TableID
      and ilt$tableoperation.recordid = :InventoryID
      and ilt$fielddate.fieldid is not null
    order by 1
    into :ATABLEOPERATIONID, :FIELDID, :FIELDVALUE, :ACREATETIME, :AUSERNAME, :AHISTORY_OPERATION
  do begin
    if (:TABLEOPERATIONID is not null and :TABLEOPERATIONID <> :ATABLEOPERATIONID) then begin
      suspend;
      if (:only_change_data = 1) then begin
        PARTYID = null;
        REGDATE = null;
        OPERATIONID = null;
        GROSS = null;
        TARE = null;
        NET = null;
        SAMPLERINDATE = null;
        PROVIDERID = null;
        SAMPLERQUALITYID = null;
        LABQUALITYID = null;
        SILAGEID = null;
        ROUTEID = null;
        STORAGEID = null;
        CONTRACTID = null;
        STATE = null;
      end
    end
    TABLEOPERATIONID = :ATABLEOPERATIONID;
    CREATETIME = :ACREATETIME;
    USERNAME = :AUSERNAME;
    HISTORY_OPERATION = :AHISTORY_OPERATION;
    if (:FIELDID = :PARTYIDID) then PARTYID = cast(:FIELDVALUE as integer);
    if (:FIELDID = :REGDATEID) then REGDATE = cast(:FIELDVALUE as date);
    if (:FIELDID = :OPERATIONIDID) then OPERATIONID = cast(:FIELDVALUE as integer);
    if (:FIELDID = :GROSSID) then GROSS = cast(:FIELDVALUE as integer);
    if (:FIELDID = :TAREID) then TARE = cast(:FIELDVALUE as integer);
    if (:FIELDID = :NETID) then NET = cast(:FIELDVALUE as integer);
    if (:FIELDID = :SAMPLERINDATEID) then SAMPLERINDATE = cast(:FIELDVALUE as date);
    if (:FIELDID = :PROVIDERIDID) then PROVIDERID = cast(:FIELDVALUE as integer);
    if (:FIELDID = :SAMPLERQUALITYIDID) then SAMPLERQUALITYID = cast(:FIELDVALUE as integer);
    if (:FIELDID = :LABQUALITYIDID) then LABQUALITYID = cast(:FIELDVALUE as integer);
    if (:FIELDID = :SILAGEIDID) then SILAGEID = cast(:FIELDVALUE as integer);
    if (:FIELDID = :ROUTEIDID) then ROUTEID = cast(:FIELDVALUE as integer);
    if (:FIELDID = :STORAGEIDID) then STORAGEID = cast(:FIELDVALUE as integer);
    if (:FIELDID = :CONTRACTIDID) then CONTRACTID = cast(:FIELDVALUE as integer);
    if (:FIELDID = :STATEID) then STATE = cast(:FIELDVALUE as integer);
  end
  suspend;
end