Обновление базы для договоров — различия между версиями

Материал из wiki.standart-n.ru
Перейти к: навигация, поиск
(→‎Выполняем скрипт)
(→‎Выполняем скрипт)
 
Строка 313: Строка 313:
 
execute procedure pr_alter_view('DOCS','DOCS_BI','if (new.agents_contract_id is null) then
 
execute procedure pr_alter_view('DOCS','DOCS_BI','if (new.agents_contract_id is null) then
 
     new.agents_contract_id = (select first 1 id from agents_contracts where agent_id = new.agent_id and status = 1);
 
     new.agents_contract_id = (select first 1 id from agents_contracts where agent_id = new.agent_id and status = 1);
−
   if (new.agents_contract_id is null) then new.agents_contract_id = 0;','TRIGGER','BEFORE INSERT')
+
   if (new.agents_contract_id is null) then new.agents_contract_id = 0;','TRIGGER','BEFORE INSERT');^
  
 
SET TERM ; ^
 
SET TERM ; ^

Текущая версия на 18:55, 13 июля 2016

Условие

Если Менеджер, а соответственно и структура базы младше 2013 года, заявку переводить на отдел внедрения или программистов

Заменить версию Менеджера

Заменить ManagerXP2.exe на версию 2.272.38 от 29.12.2014 г. или более новую.

Установить процедуру PR_ALTER_VIEW

PR_ALTER_VIEW

Выполняем скрипт



SET TERM ^ ;
/*создание доменов если их нет*/
execute procedure PR_ALTER_VIEW('DM_ID_NULL','','BIGINT','DOMAIN');^
execute procedure PR_ALTER_VIEW('DM_ID','','BIGINT NOT NULL','DOMAIN');^
execute procedure PR_ALTER_VIEW('DM_STATUS','','INTEGER','DOMAIN');^
execute procedure PR_ALTER_VIEW('DM_DATETIME','','TIMESTAMP','DOMAIN');^
execute procedure PR_ALTER_VIEW('DM_TEXT1024','','VARCHAR(1024) CHARACTER SET WIN1251 COLLATE WIN1251','DOMAIN');^
execute procedure PR_ALTER_VIEW('DM_DOUBLE','','DOUBLE PRECISION','DOMAIN');^
execute procedure PR_ALTER_VIEW('DM_ID_NULL','','BIGINT','DOMAIN');^
execute procedure PR_ALTER_VIEW('DM_TEXT','','VARCHAR(250) CHARACTER SET WIN1251 COLLATE PXW_CYRL ','DOMAIN');^

execute procedure PR_ALTER_VIEW('','','GEN_AGENTS_CONTRACTS_STATUS_ID','generator');^

/* таблица AGENTS_CONTRACTS_STATUS*/
EXECUTE BLOCK AS BEGIN
if (not exists(select 1 from rdb$relations where rdb$relation_name = 'AGENTS_CONTRACTS_STATUS')) then
execute statement '
CREATE TABLE AGENTS_CONTRACTS_STATUS (
    ID       DM_STATUS NOT NULL /* DM_STATUS = INTEGER */,
    CAPTION  DM_TEXT /* DM_TEXT = VARCHAR(250) */
);';
END^

/* Trigger: AGENTS_CONTRACTS_STATUS_BI */
CREATE OR ALTER TRIGGER AGENTS_CONTRACTS_STATUS_BI FOR AGENTS_CONTRACTS_STATUS
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gen_agents_contracts_status_id,1);
end
^

delete from AGENTS_CONTRACTS_STATUS;^
INSERT INTO AGENTS_CONTRACTS_STATUS (ID, CAPTION)
                             VALUES (0, 'Активный');^
INSERT INTO AGENTS_CONTRACTS_STATUS (ID, CAPTION)
                             VALUES (-1, 'Удален');^
INSERT INTO AGENTS_CONTRACTS_STATUS (ID, CAPTION)
                             VALUES (1, 'Основной');^

execute procedure PR_ALTER_VIEW('','','GEN_AGENTS_CONTRACTS_ID','generator');^
execute procedure PR_ALTER_VIEW('','','GEN_AGENTS_CONTRACTS_VNUM','generator');^

/* таблица AGENTS_CONTRACTS*/
EXECUTE BLOCK AS BEGIN
if (not exists(select 1 from rdb$relations where rdb$relation_name = 'AGENTS_CONTRACTS')) then
execute statement '
CREATE TABLE AGENTS_CONTRACTS (
    ID             DM_ID NOT NULL /* DM_ID = BIGINT */
    );';
END^
execute procedure PR_ALTER_VIEW('AGENTS_CONTRACTS','ID','','PRIMARY KEY');^

execute procedure PR_ALTER_VIEW('AGENTS_CONTRACTS','PARENT_ID','DM_ID_NULL','TABLE');^
execute procedure PR_ALTER_VIEW('AGENTS_CONTRACTS','AGENT_ID','DM_ID','TABLE');^
execute procedure PR_ALTER_VIEW('AGENTS_CONTRACTS','VNUM','DM_ID_NULL','TABLE');^
execute procedure PR_ALTER_VIEW('AGENTS_CONTRACTS','CONTRACT_DATE','DM_DATETIME','TABLE');^
execute procedure PR_ALTER_VIEW('AGENTS_CONTRACTS','CAPTION','DM_TEXT1024','TABLE');^
execute procedure PR_ALTER_VIEW('AGENTS_CONTRACTS','STATUS','DM_STATUS','TABLE');^
execute procedure PR_ALTER_VIEW('AGENTS_CONTRACTS','START_DATE','DM_DATETIME','TABLE');^
execute procedure PR_ALTER_VIEW('AGENTS_CONTRACTS','END_DATE','DM_DATETIME','TABLE');^
execute procedure PR_ALTER_VIEW('AGENTS_CONTRACTS','BASE_TYPE','DM_STATUS','TABLE');^
execute procedure PR_ALTER_VIEW('AGENTS_CONTRACTS','SUMMA','DM_DOUBLE','TABLE');^
execute procedure PR_ALTER_VIEW('AGENTS_CONTRACTS','BASE_AGENT_ID','DM_ID_NULL','TABLE');^
execute procedure PR_ALTER_VIEW('AGENTS_CONTRACTS','BARCODE','DM_TEXT1024','TABLE');^
execute procedure PR_ALTER_VIEW('AGENTS_CONTRACTS','DELETEDT','DM_DATETIME','TABLE');^

/* Trigger: AGENTS_CONTRACTS_BI */
CREATE OR ALTER TRIGGER AGENTS_CONTRACTS_BI FOR AGENTS_CONTRACTS
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gen_agents_contracts_id,1);

  if (new.vnum is null) then
    new.vnum = gen_id(gen_agents_contracts_vnum,1);

  if (new.caption is null) then
    new.caption = 'Договор №'||new.vnum;

end^

/* Trigger: AGENTS_CONTRACTS_BU0 */
CREATE OR ALTER TRIGGER AGENTS_CONTRACTS_BU0 FOR AGENTS_CONTRACTS
ACTIVE BEFORE UPDATE POSITION 0
AS
begin
  if (new.status = 1) then
    update agents_contracts set status = 0 where status = 1;
end^

CREATE OR ALTER VIEW VW_AGENTS_CONTRACTS(
    ID,
    PARENT_ID,
    AGENT_ID,
    AGENT_CAPTION,
    VNUM,
    CONTRACT_DATE,
    CAPTION,
    STATUS,
    STATUS_CAPTION,
    START_DATE,
    END_DATE,
    BASE_TYPE,
    SUMMA,
    BASE_AGENT_ID,
    BARCODE,
    DELETEDT)
AS
select
    ac.id,
    ac.parent_id,
    ac.agent_id,
    a.caption,
    ac.vnum,
    ac.contract_date,
    ac.caption,
    ac.status,
    acs.caption,
    ac.start_date,
    ac.end_date,
    ac.base_type,
    ac.summa,
    ac.base_agent_id,
    ac.barcode,
    ac.deletedt
from agents_contracts ac
left join agents a on ac.agent_id = a.id
left join agents_contracts_status acs on ac.status = acs.id ;^

execute procedure PR_ALTER_VIEW('DOCS','AGENTS_CONTRACT_ID','DM_ID_NULL','TABLE');^

/* Добавить Справочник "Контракты" */
EXECUTE BLOCK AS BEGIN
if ((select caption from SP$WDICTS where caption = 'Контракты') is null) then
execute statement 'INSERT INTO SP$WDICTS (ID, PARENT_ID, CAPTION, DESCRIPTION, SID, STATUS, INI, BMP, SORTING, FRAMECLASS) VALUES ((select max(id)+1 from SP$WDICTS), 0, ''Контракты'', NULL, ''AGENTS_CONTRACTS'', -1, ''
[insertsql]
insert into agents_contracts (agent_id, status) values (:agent_id:, 0)

[deletesql_selected]

[deletesql]
update agents_contracts set deletedt=current_date, status = iif(status = -1, 0, -1) where id=:id

[refreshsql]
select * from vw_agents_contracts where id=:id

[selectsqlwithdeleted]
select * from vw_agents_contracts where agent_id=:agent_id: order by id, caption

[selectsql]
select * from vw_agents_contracts where status <> -1 and agent_id=:agent_id: order by id, caption

[main]
sourcetablename=AGENTS_CONTRACTS
returnfieldname=id
captionfieldname=caption
keyfieldname=id
ViewID=agents_contracts
RootGroupTableName=
ShowCaption=Договора
GetCaption=Договора
GroupSelect=0
foldergroup=
initfolder_id=
dataset=0
InitTMSGroup_id=829
folders_visible=0
Canfloating=0
hidetoppanel=0
ActivateDictAction=0

[cfSelect]
selectfieldexpression=caption
AllwaysPartial=1

[form_show]
position=2
left=0
Top=0
Width=900
Height=600
MaxWidth=0
MaxHeight=0
MinWidth=500
MinHeight=400

[form_get]
position=2
left=0
Top=0
Width=900
Height=600
MaxWidth=0
MaxHeight=0
MinWidth=500
MinHeight=400

[childs]
bottomdock_units=0
bottomdock_size=0
rightdock_units=0
rightdock_size=0

[editfields]
VNUM=default
CONTRACT_DATE=default
CAPTION=default
START_DATE=default
END_DATE=default
BASE_TYPE=default
SUMMA=default
BASE_AGENT_ID=default
BARCODE=default
STATUS=WDICTS.AGENTS_CONTRACTS_STATUS(initvalue=status)
status_caption=status
'', NULL, NULL, NULL);';
END^

/* Добавить Справочник "Контракты статусы" */
EXECUTE BLOCK AS BEGIN
if ((select caption from SP$WDICTS where caption = 'Контракты статусы') is null) then
execute statement 'INSERT INTO SP$WDICTS (id, PARENT_ID, CAPTION, DESCRIPTION, SID, STATUS, INI, BMP, SORTING, FRAMECLASS) VALUES ((select max(id)+1 from SP$WDICTS),0, ''Контракты статусы'', NULL, ''AGENTS_CONTRACTS_STATUS'', 0, ''
[insertsql]

[deletesql_selected]

[deletesql]

[refreshsql]
select * from agents_contracts_status where id=:id

[selectsqlwithdeleted]

[selectsql]
select * from agents_contracts_status where id <> -1 order by id

[main]
sourcetablename=AGENTS_CONTRACTS_STATUS
returnfieldname=id
captionfieldname=caption
keyfieldname=id
ViewID=agents_contracts_status
RootGroupTableName=
ShowCaption=Статус
GetCaption=Статус
GroupSelect=0
foldergroup=
initfolder_id=
dataset=0
InitTMSGroup_id=0
folders_visible=0
Canfloating=0
hidetoppanel=0
ActivateDictAction=0

[cfSelect]
selectfieldexpression=caption
AllwaysPartial=1

[form_show]
position=8
left=0
Top=0
Width=300
Height=200
MaxWidth=0
MaxHeight=0
MinWidth=300
MinHeight=200

[form_get]
position=8
left=0
Top=0
Width=300
Height=200
MaxWidth=0
MaxHeight=0
MinWidth=300
MinHeight=200

[childs]
bottomdock_units=0
bottomdock_size=0
rightdock_units=0
rightdock_size=0

[editfields]
CAPTION=default

[addfields]
CAPTION=default
'', NULL, NULL, NULL);';
END^

execute procedure pr_alter_view('DOCS','DOCS_BI','if (new.agents_contract_id is null) then
    new.agents_contract_id = (select first 1 id from agents_contracts where agent_id = new.agent_id and status = 1);
  if (new.agents_contract_id is null) then new.agents_contract_id = 0;','TRIGGER','BEFORE INSERT');^

SET TERM ; ^


/*execute procedure PR_ALTER_VIEW('VW_DOCS','AGENT_CONTRACT_CAPTION','w.mgn_id','VIEW'); join пока нет возможности в автоматическом режиме добавлять*/

Добавить в вид VW_DOCS поле

  AGENT_CONTRACT_CAPTION
источник
  ac.caption
где, ас
  left join agents_contracts ac on docs.agents_contract_id = ac.id

Возможно необходимо будет заменить строку:
  iif(dt.base_type=1,summa_o,summa)
на
  iif(dt.base_type=1,docs.summa_o,docs.summa)


Сетки

Прикрепленный файл Файл:Сетка контракты.zip

Проверка себя

 Зайдите в журнал документов, откройте шапку любого документа, создайте новый договор, измените его статус, если ошибки нет, значит вы все правильно настроили.

Теги: qWork: Field 'contract_caption' not found, Договора, Менеджер

Источник — «http://wiki.standart-n.ru/index.php?title=Обновление_базы_для_договоров&oldid=4288»