Обновление базы для договоров
Материал из wiki.standart-n.ru
Версия от 13:11, 20 февраля 2016; Aleksnick (обсуждение | вклад)
Содержание
Условие
Если Менеджер, а соответственно и структура базы младше 2013 года, заявку переводить на отдел внедрения или программистов
Заменить версию Менеджера
Заменить ManagerXP2.exe на версию 2.272.38 от 29.12.2014 г. или более новую.
Выполнить скрипт
/******************************************************************************/ /*** Generated by IBExpert 14.01.2015 13:32:09 ***/ /******************************************************************************/ /******************************************************************************/ /*** Following SET SQL DIALECT is just for the Database Comparer ***/ /******************************************************************************/ SET SQL DIALECT 3; /******************************************************************************/ /*** Tables ***/ /******************************************************************************/ CREATE GENERATOR GEN_AGENTS_CONTRACTS_STATUS_ID; CREATE TABLE AGENTS_CONTRACTS_STATUS ( ID DM_STATUS NOT NULL /* DM_STATUS = INTEGER */, CAPTION DM_TEXT /* DM_TEXT = VARCHAR(250) */ ); /******************************************************************************/ /*** Triggers ***/ /******************************************************************************/ SET TERM ^ ; /******************************************************************************/ /*** Triggers for tables ***/ /******************************************************************************/ /* 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 ^ SET TERM ; ^ /******************************************************************************/ /*** Privileges ***/ /******************************************************************************/ 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, 'Основной'); COMMIT WORK; /******************************************************************************/ /*** Generated by IBExpert 14.01.2015 13:32:54 ***/ /******************************************************************************/ /******************************************************************************/ /*** Following SET SQL DIALECT is just for the Database Comparer ***/ /******************************************************************************/ SET SQL DIALECT 3; /******************************************************************************/ /*** Tables ***/ /******************************************************************************/ CREATE GENERATOR GEN_AGENTS_CONTRACTS_ID; CREATE GENERATOR GEN_AGENTS_CONTRACTS_VNUM; CREATE TABLE AGENTS_CONTRACTS ( ID DM_ID NOT NULL /* DM_ID = BIGINT */, PARENT_ID DM_ID_NULL /* DM_ID_NULL = BIGINT */, AGENT_ID DM_ID /* DM_ID = BIGINT */, VNUM DM_ID_NULL /* DM_ID_NULL = BIGINT */, CONTRACT_DATE DM_DATETIME /* DM_DATETIME = TIMESTAMP */, CAPTION DM_TEXT1024 /* DM_TEXT1024 = VARCHAR(1024) */, STATUS DM_STATUS /* DM_STATUS = INTEGER */, START_DATE DM_DATETIME /* DM_DATETIME = TIMESTAMP */, END_DATE DM_DATETIME /* DM_DATETIME = TIMESTAMP */, BASE_TYPE DM_STATUS /* DM_STATUS = INTEGER */, SUMMA DM_DOUBLE /* DM_DOUBLE = DOUBLE PRECISION */, BASE_AGENT_ID DM_ID_NULL /* DM_ID_NULL = BIGINT */, BARCODE DM_TEXT1024 /* DM_TEXT1024 = VARCHAR(1024) */, DELETEDT DM_DATETIME /* DM_DATETIME = TIMESTAMP */ ); /******************************************************************************/ /*** Primary Keys ***/ /******************************************************************************/ ALTER TABLE AGENTS_CONTRACTS ADD CONSTRAINT PK_AGENTS_CONTRACTS PRIMARY KEY (ID); /******************************************************************************/ /*** Triggers ***/ /******************************************************************************/ SET TERM ^ ; /******************************************************************************/ /*** Triggers for tables ***/ /******************************************************************************/ /* 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 ^ SET TERM ; ^ /******************************************************************************/ /*** Fields descriptions ***/ /******************************************************************************/ COMMENT ON COLUMN AGENTS_CONTRACTS.STATUS IS '0 активный 1 основной -1 удаленный'; /******************************************************************************/ /*** Privileges ***/ /******************************************************************************/ 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 ; ALTER TABLE DOCS ADD AGENTS_CONTRACT_ID DM_ID_NULL; /* Добавить Справочник "Контракты" */ INSERT INTO SP$WDICTS (PARENT_ID, CAPTION, DESCRIPTION, SID, STATUS, INI, BMP, SORTING, FRAMECLASS) VALUES (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); /* Добавить Справочник "Контракты статусы" */ INSERT INTO SP$WDICTS (PARENT_ID, CAPTION, DESCRIPTION, SID, STATUS, INI, BMP, SORTING, FRAMECLASS) VALUES (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);
Добавить в конец триггера 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;
Добавить в вид 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, Договора, Менеджер