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

Версия 14:22, 14 января 2015

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

Заменить 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

Добавить в таблицу DOCS поле

agents_contract_id тип DM_ID_NULL

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

 AGENT_CONTRACT_CAPTION

источник

 ac.caption

где, ас

 left join agents_contracts ac on docs.agents_contract_id = ac.id

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

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

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