Установка периодичной нумерации — различия между версиями

Текущая версия на 15:19, 13 января 2017

В сервис параметры системы устанавливаем параметр Автоматизация - Периодичность нумерации - Год

Выполнить скрипт

INSERT INTO PARAMS (PARENT_ID, PARAM_ID, PARAM_CAPTION, PARAM_TYPE, PARAM_VALUE, AUDIT_ID, IMAGEINDEX, STATUS, INSERTDT, SORTING, PARAM_TYPE_DATA)
            VALUES (-30, 'PERIOD_NUM', 'Периодичность нумерации', 14, '1', 0, NULL, NULL, '2017-01-13 13:05:51', NULL, 'FIXEDITEMS SHOWONLYVALUES RETURNNAME
0=Без ограничений
1=В пределах года
2=В пределах месяца');

COMMIT WORK;

SET SQL DIALECT 3;



SET TERM ^ ;



CREATE OR ALTER TRIGGER DOCS_BI0 FOR DOCS
ACTIVE BEFORE INSERT POSITION 0
AS
declare variable PERIOD_NUM dm_text;
begin
  if (new.doc_type not in (3,9) and new.agent_id<>-1) then
  select param_value from params where param_id='PERIOD_NUM' into :PERIOD_NUM;
  if (PERIOD_NUM='1') then
    if (not exists (select id from docs d where d.doc_type=new.doc_type and
    d.commitdate between '01.01.'||extract (year from new.commitdate) and '31.12.'||extract (year from new.commitdate)
    and d.status=1)) then
    begin
      new.vshift=new.vshift+1;
      if (cast(new.vnum as dm_text)=new.docnum) then
        new.docnum='1';
      new.vnum=1;
    end
  else if (PERIOD_NUM='2') then
    if (not exists (select id from docs d where d.doc_type=new.doc_type and
    d.commitdate between '01.'||lpad(extract (month from new.commitdate),2)||'.'||extract (year from new.commitdate)
    and addmonth('01.'||lpad(extract (month from new.commitdate),2)||'.'||extract (year from new.commitdate),1)
    and d.status=1)) then
    begin
      new.vshift=new.vshift+1;
      if (cast(new.vnum as dm_text)=new.docnum) then
        new.docnum='1';
      new.vnum=1;
    end

  select caption || ' №' || new.docnum from doc_types where id=new.doc_type into new.caption;
end
^

SET TERM ; ^